Same datetime giving two different timestamps

[gnznroses]

This is very odd. Running this sql statement on both my local machine and on my server gives two different results:

SELECT UNIX_TIMESTAMP(signup_date) AS signup_ts, signup_date
          FROM users
          WHERE id = 123

on the local machine (Windows) it produces:
signup_ts
1239248570
signup_date
2009-04-08 23:42:26

on my server (linux):
signup_ts
1239248546
signup_date
2009-04-08 23:42:26

[CFMaBiSmAd]

Probably because the timezone setting is different on the two servers. The conversion to/from a Unix Timestamp is done using the current time zone setting. As long as the timezone is set the same for both the to/from conversion, you will usually get back the value you put in.

If you are in a location that observes DST (daylight savings time) and the DST start/end times have changed since you stored the Unix Timestamp, you will find that unless the DST database on your server is kept current that you will experience an error in the 'from' conversion.

The need for the to/from timezone to be the same and for the DST database to be kept up to date makes using a Unix Timestamp problematic for dates/times that you expect to always be the value they were when you stored them. If you use a DATETIME data type, the value you put into it will always be the value you put into it because it does not need to undergo a conversion.

This what the mysql has to say about using a Unix Timestamp -

Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes, it is possible for two UNIX_TIMESTAMP() to map two TIMESTAMP values to the same Unix timestamp value. FROM_UNIXTIME() will map that value back to only one of the original TIMESTAMP values.

There are also a couple dozen date/time functions you can directly use on a DATE/DATETIME/TIME data type.

[gnznroses]

thanks.

hmm, both my pc and my server are in EST. the difference in the numbers is 24 seconds though, so wouldn't it be at least 1 hour if it were related to timezones?

so, i should not rely on the timestamp always being the same from this point forward? because i am using the timestamp (converted from the datetime) for encryption purposes, and i would need the number to remain contstant at all times. if we were to change or upgrade servers it may affect the conversion?

if needed i can save the timestamp value in a separate column to ensure that it remains constant.

[CFMaBiSmAd]

lol, I did not look at the actual different between the timestamps.

The conversion uses the current time on the server to determine the number of seconds since Unix Epoch. If the server's time is a few seconds off, the timestamp produced will be off too.

[gnznroses]

That crossed my mind, but then I decided that that was wrong, and I think it is?
Because it's not the current timestamp, but the timestamp of the stored datetime. So I would imagine that it calculates the difference between the datetime and the epoch -- the current server time should never comes into play.

[Old Pedant]

Yeah, CFM. Look again at his query:

SELECT UNIX_TIMESTAMP(signup_date) AS signup_ts, signup_date
          FROM users
          WHERE id = 123

On both machines, the value of signup_date is showing up the same:

2009-04-08 23:42:26

And yet the result of the call to the UNIX_TIMESTAMP( ) function is different.

So let's see if JavaScript can tell us which one is right:

alert( new Date(1239248546) );
-- produces Wed Apr 8 20:42:26 PDT 2009

alert( new Date(1239248570) );
-- produces Wed Apr 8 20:42:50 PDT 2009

So clearly the Linux server is correct. A bug in the implementation of the function's code under Windows, perhaps???

It does occur to me that I remember reading that we "lost" a second at the last New Year. That is, to make midnight really *be* midnight, they dropped one second from the calendar. I wonder if it's possible there have been 24 such adjustments in the last 39 years, and so Windows is presenting the calendar-correct view, as opposed to the always-60-seconds-per-minute view??

Seems unlikely there have been that many adjustments, but...

[Old Pedant]

Of course, even if Windows *is* doing that, it's wrong. By definition, since the function is named Unix_TimeStamp, the code on Windows should be doing what Unix says to do.

[Old Pedant]

Here's an interesting experiment:

Put in date/time values of 31 Dec 2008 23:59:59 and 1 Jan 2009 00:00:01 into a couple of records and see if both servers report a difference of 2 seconds or not.

[CFMaBiSmAd]

Php's strtotime() produces the 1239255746 result on Windows. So the issue is within mysql. Edit: I just did SELECT UNIX_TIMESTAMP('2009-04-08 23:42:26') on mysql 5.1.39 community under Windows and it gives 1239255746 as well. So, the issue would appear to be your mysql version under Windows.

Back to your problem, if you are attempting to make a unique identifier for each account, use something like php's uniqid() function and store the value you get in your table.

[Old Pedant]

I just did SELECT UNIX_TIMESTAMP('2009-04-08 23:42:26') on mysql 5.1.39 community under Windows and it gives 1239255746 as well

Fascinating!

Well, you have to admit I came up with a really creative (but probably silly) explanation for a possible discrepancy.

use something like php's uniqid() function and store the value you get in your table.

Definitely agree. SQL Server even has a NEWID() function built in (and a DB datatype to match) for just this purpose.

[gnznroses]

Hmm, interesting.
Well, at least I know it's just a Windows problem apparently.
Our server will always be Linux, so I think we're OK.