Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Sep 2002
    Posts
    64
    Thanks
    3
    Thanked 1 Time in 1 Post

    Question Same datetime giving two different timestamps

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

    Code:
    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

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    New Coder
    Join Date
    Sep 2002
    Posts
    64
    Thanks
    3
    Thanked 1 Time in 1 Post
    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.

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    New Coder
    Join Date
    Sep 2002
    Posts
    64
    Thanks
    3
    Thanked 1 Time in 1 Post
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Yeah, CFM. Look again at his query:
    Code:
    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:
    Code:
    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:
    Code:
    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...

  • Users who have thanked Old Pedant for this post:

    gnznroses (10-10-2009)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.

  • #9
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    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.
    Last edited by CFMaBiSmAd; 10-09-2009 at 11:59 PM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    gnznroses (10-10-2009)

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.
    Last edited by Old Pedant; 10-10-2009 at 12:25 AM.

  • #11
    New Coder
    Join Date
    Sep 2002
    Posts
    64
    Thanks
    3
    Thanked 1 Time in 1 Post
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •