Jump to content

datetime to unix timestamp?


aebstract

Recommended Posts

$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]);

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.