datetime to unix timestamp?

[aebstract]

I have a datetime value: Sep 1 2006 12:05AM,

I'm needing to change it to a unix timestamp format.

The correct timestamp would be: 1157087100

 

I've searched around and tried a few things but can't find anything that seems to be solid.

[cristalmolix]

http://uk.php.net/strtotime

[aebstract]

$r[5] = strtotime("$r[5]");

 

Returns a timestamp of  1157083500, which is 08 / 31 / 06 @ 11:05:00pm. It's suppose to be Sep 1 2006 12:05AM.

 

 

 

edit: This gives the same result as just doing strtotime();

$r[5] = preg_replace('/:[0-9][0-9][0-9]/','',$r[5]);
$r[5] = strtotime($r[5]);

[PFMaBiSmAd]

Functions like strtotime() and date() are dependent on the current time zone setting and any DST settings or even if the DST database that php is using is up to date (a lot of locations have changed the DST start/stop dates) and you will see a lot of problems with the conversion into or out of a Unix Timestamp.

 

If you want a date/time to always be the correct value that was entered, store it as a DATETIME (YYYY-MM-DD HH:MM:SS.) No conversion is necessary to use the value (changing a DATETIME formated value to something else involves simple lookups and simple math, no error prone and time zone dependent conversion is needed.)

[aebstract]

My problem is that I'm pulling a lot of information out of a MSSQL database from one setup in to a new setup in a MYSQL database. The database structures are already setup, so I can't change them. That's why I'm trying to get the conversion. It's saved as a datetime field where I'm trying to pull from, but I need to enter it in to the new database as a unix timestamp.

 

edit: Exactly what format should this be put in? Can use some regex or something to get it in the right format to do a clean conversion, no?

[aebstract]

bump, back to this

[cags]

As PFMaBiSmAd already mentioned, it's not a problem with the format it's a problem with the timezone. Regardless of whether you do it with Regex, String functions or whatever you will have the same issue when converting between timestamp and date formats.

 

Example:

echo date('Y-m-d H:i:s', 1157087100 );

 

Output (on my system):

2006-09-01 06:05:00

 

Which as you can see uses the same timestamp as you used, but doesn't give the same value as either what your getting or what you think the answer should be. A unix timestamp is stored as UTC ie ~GMT. When you use date it converts this value to whatever that time translates to in the current timezone of the server.

 

If this is a one time thing you will just have to calculate the difference between your timezone and the required result and add/subtract the value as appropriate.

[PFMaBiSmAd]

The format you showed in the first post is NOT a DATETIME value in a msyql database. Converting that to an actual DATETIME (YYYY-MM-DD HH:MM:SS) is what you should be attempting. You could then sort the data by dates and times, do greater-than/less-then comparisons, use the two dozen mysql date/time functions on it, and more importantly, you could directly do queries that return results for a range of dates, grouped by human readable time periods, like days, months, and years... within that range of dates.

 

What exact problem are you having that makes you think you want to convert these values into Unix Timestamps and that the sample value you have posted is and will always result in the Unix Timestamp that you posted?

[PFMaBiSmAd]

The Unix Timestamp you have been using in this example is 2006-09-01 05:05:00AM GMT

[cags]

It's 06:05AM GMT not 05:05 GMT isn't it?!

 

2006-09-01 06:05:00AM GMT

[PFMaBiSmAd]

I used gmdate('Y-m-d H:i:s', 1157087100 ); to come to that conclusion. Php.net has had a number of reoccurring bugs in its functions (the change log reads like a clown act at times.) It's hard to say for a fact that the gm___ functions would produce the correct value.

 

All the more reason to not use a Unix Timestamp.

[aebstract]

I ended up getting something to somewhat work, I went through and checked a lot of values to make sure they were right or at least close, for what I'm doing if they are at least close then I'll be okay. Hopefully no big issues arise.

[cags]

@PFMaBiSmAd

I just realised the OP has used at least 3 different timestamps in this thread, we were probably checking different values.