regex to format a date for mysql... separated with hyphens 1

[pushyr]

i'm trying to format a date for mysql

the date is is currently 20100201 but i'd like to format it as 2010-02-01, separated with hyphens

what would be the ideal regex code to do this?

 

[feherke]

Hi

[COLOR=darkgoldenrod]preg_replace[/color][teal]([/teal][green][i]'/(....)(..)(..)/'[/i][/green][teal],[/teal][green][i]'\\1-\\2-\\3'[/i][/green][teal],[/teal][green][i]'20100201'[/i][/green][teal]);[/teal]

Feherke.

http://free.rootshell.be/~feherke/

 

[pushyr]

cheers feherke... sorted!!

 

[jpadie]

@pushyr
why do you want to use a regex for this?

 

[pushyr]

i'm auto uploading csv files to a table. on the end of the file name i have the date for example...

FileName20101130_20101206.csv

by appending the date to the file, i just need to break the date apart and i have my date range for each record

 

[jpadie]

my question is still why you would want a regex for this?

three things to keep in mind:

1. you can split the date easily with a set of substr commands. which would be computationally less expensive than a regex.
2. mysql can handle dates just fine in the YYYYMMDD format.
3. strtotime can handle dates justin fine in the YYYYMMDD format which will be useful if you need to do any manipulations on the resultant date value (as strtotime converts to a unix timestamp)

so assuming no manips are needed i would do something like this

list($start, $end) = explode('_', substr($filename, -21, 17));

you then have the start and end date in an easily digestable form for wordpress.

 

[jpadie]

and when I say wordpress I mean mysql.

 

[feherke]

Hi

[tt][blue][small][ignore][off-topic][/ignore][/small][/blue][/tt]

3. strtotime can handle dates [red]justin fine[/red] in the YYYYMMDD format

Abit egocentric, aren't you ?
[tt][blue][small][ignore][/off-topic][/ignore][/small][/blue][/tt]

Feherke.

http://free.rootshell.be/~feherke/

 

[jpadie]

oh dear.
justin clearly not fine ...

 

[pushyr]

Hi jpadie,

for step one... bear in mind my date formats vary so i could have...

FileName2010113020101206.csv
FileName20101130.20101206.csv
FileName20101130_20101206.csv

so could your solution work for these these alternatives?

i guess it's the 2010113020101206 option that is the difficult one

i didn't realize mysql could handle YYYYMMDD, that would definitely reduce the work load

Cheers!

 

[feherke]

Hi

Then I would change to [tt]preg_match()[/tt] :

[COLOR=darkgoldenrod]preg_match[/color][teal]([/teal][green][i]'/(\\d{4})(\\d{2})(\\d{2})[_.]?(\\d{4})(\\d{2})(\\d{2})/'[/i][/green][teal],[/teal][navy]$filename[/navy][teal],[/teal][navy]$m[/navy][teal]);[/teal]
[navy]$start[/navy][teal]=[/teal][COLOR=darkgoldenrod]implode[/color][teal]([/teal][green][i]'-'[/i][/green][teal],[/teal][COLOR=darkgoldenrod]array_slice[/color][teal]([/teal][navy]$m[/navy][teal],[/teal][purple]1[/purple][teal],[/teal][purple]3[/purple][teal]));[/teal]
[navy]$end[/navy][teal]=[/teal][COLOR=darkgoldenrod]implode[/color][teal]([/teal][green][i]'-'[/i][/green][teal],[/teal][COLOR=darkgoldenrod]array_slice[/color][teal]([/teal][navy]$m[/navy][teal],[/teal][purple]4[/purple][teal],[/teal][purple]3[/purple][teal]));[/teal]

Feherke.

http://free.rootshell.be/~feherke/

 

[pushyr]

cool... but how would i pregmatch the filename. right now i'm using pregreplace to rid the start, end date and file .ext at the end of the string

and, for example my filename could contain any number of lower and upper case character as well numbers thrown in somewhere, like...

FileName02
02MayFilename
File323sdFilename32

etc..

 

[jpadie]

pushyr, you keep changing the goal posts here. you need to tie down the different file name formats that you will accept. then we can provide a solution. If there is an infinite universe of potential filename formats then there is an infinitely small possibility of an holistic solution.

 

[pushyr]

sorry i meant files names could be like this...

FileName022010113020101206.csv
02MayFilename2010113020101206.csv
File323sdFilename322010113020101206.csv

so how could the file name i.e 'FileName02' be pregmatched?

 

[feherke]

Hi

Have you tried anything based on my suggestion at 11 Feb 10 6:10 ?

[COLOR=darkgoldenrod]preg_match[/color][teal]([/teal][green][i]'/[highlight](.*)[/highlight](\\d{4})(\\d{2})(\\d{2})[_.]?(\\d{4})(\\d{2})(\\d{2})/'[/i][/green][teal],[/teal][navy]$filename[/navy][teal],[/teal][navy]$m[/navy][teal]);[/teal]
[highlight][navy]$name[/navy][teal]=[/teal][navy]$m[/navy][teal][[/teal][purple]1[/purple][teal]];[/teal][/highlight]
[navy]$start[/navy][teal]=[/teal][COLOR=darkgoldenrod]implode[/color][teal]([/teal][green][i]'-'[/i][/green][teal],[/teal][COLOR=darkgoldenrod]array_slice[/color][teal]([/teal][navy]$m[/navy][teal],[/teal][purple][highlight]2[/highlight][/purple][teal],[/teal][purple]3[/purple][teal]));[/teal]
[navy]$end[/navy][teal]=[/teal][COLOR=darkgoldenrod]implode[/color][teal]([/teal][green][i]'-'[/i][/green][teal],[/teal][COLOR=darkgoldenrod]array_slice[/color][teal]([/teal][navy]$m[/navy][teal],[/teal][purple][highlight]5[/highlight][/purple][teal],[/teal][purple]3[/purple][teal]));[/teal]

Feherke.

http://free.rootshell.be/~feherke/

 

[pushyr]

yes i did. was just looking for a way to do it in reverse.

Cheers!

 

[pushyr]

hey guys... i enjoyed that... you taught me quite a bit!!!

 

[jpadie]

all those files are congruent as to the date parts. none have interstitial elements

so you can easily extract the dates as follows

$start = substr($filename, -20, 8);
$end = substr ($filename, -12, 8);

 

[pushyr]

your right jpadie!

but a message to feherke... your earlier regex tutorials were not wasted. definitely filed to be re-used appropriately!

 

[feherke]

Hi

$start = substr($filename, -2[red]0[/red], 8);

There you will have to use either -20 or -21, depending on the absence or presence of the separator character between the two dates. So the generic solution would be something like this :

[navy]$start[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]substr[/color][teal]([/teal][navy]$filename[/navy][teal],[/teal] [highlight][COLOR=darkgoldenrod]strpos[/color][teal]([/teal][green][i]'_.'[/i][/green][teal],[/teal][COLOR=darkgoldenrod]substr[/color][teal]([/teal][navy]$filename[/navy][teal],-[/teal][purple]13[/purple][teal],[/teal][purple]1[/purple][teal]))===[/teal]false[teal]?[/teal][/highlight][teal]-[/teal][purple]20[/purple][highlight][teal]:-[/teal][purple]21[/purple][/highlight][teal],[/teal] [purple]8[/purple][teal]);[/teal]

In which case I would definitely prefer the regular expression way.

Feherke.

http://free.rootshell.be/~feherke/