Menu
I've been trying to use the following in mySQL to create a unix stamp for current UTC time
UNIX_TIMESTAMP(UTC_TIMESTAMP()) When I execute the query and get the result, it seems like mySQL is doing UTC conversation twice.
Eg. local time 9:07PM Query above returned: 1374390482, which is next day 07:08:02 GMT, which is correct,
However, UNIX_TIMESTAMP(LOCALTIMESTAMP()) returns:
1374372551 02:09:11 GMT which is the correct UTC unix timestamp.
So UNIX_TIMESTAMP automatically translates the date object's timezone to UTC (regardless if it's already UTC or not?)
Is there a better way or a single command just to get a UTC unix timestamp in mySQL?
410
The MYSQL UTC_TIMESTAMP() function is used to get the current UTC date and time value. The resultant value is a string or a numerical value based on the context and, the value returned will be in the 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format.
Use the getTime() method to get a UTC timestamp, e.g. new Date(). getTime() . The method returns the number of milliseconds since the Unix Epoch and always uses UTC for time representation. Calling the method from any time zone returns the same UTC timestamp.
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server's time.
Unix timestamps are always based on UTC (otherwise known as GMT). It is illogical to think of a Unix timestamp as being in any particular time zone. Unix timestamps do not account for leap seconds.
Try just:
UNIX_TIMESTAMP() And see here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
