44

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?

SnareChops
  • 13,175
  • 9
  • 69
  • 91
littlejedi
  • 969
  • 1
  • 13
  • 23

1 Answers1

82

Try just:

UNIX_TIMESTAMP()

And see here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

Alden
  • 6,553
  • 2
  • 36
  • 50
  • thanks a lot, I just tried it and I think it's exactly what I need. – littlejedi Jul 21 '13 at 02:45
  • 1
    Does UNIX_TIMESTAMP() return the timestamp in the UTC or local timezone? – Jo Smo Mar 29 '14 at 08:33
  • UTC. From the docs: "returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer" – Alden Mar 29 '14 at 12:46
  • this cheatsheet might come in handy http://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc/19075291#19075291 – Timo Huovinen Feb 19 '15 at 09:24
  • 4
    @JoSmo Actually the Unix Timestamp has **no inherent timezone**. It's a time notation that's specifically designed to be _not_ bound by concepts like timezones, daylight savings and clock divisions. It's very simply a number: the number of seconds (or milliseconds) since the moment in time that can be referred to as '1970-01-01 00:00:00' UTC (which is Unix time `0`). But Unix time itself is not UTC, it transcends the concept of timezone itself. No matter which timezone you're in, the current Unix time is exactly the same for you, someone on a different timezone, and the entire universe. – ADTC Nov 22 '17 at 21:01
  • @ADTC then why does UNIX_TIMESTAMP() and UNIX_TIMESTAMP(UTC_TIMESTAMP()) returns different values: `UNIX_TIMESTAMP(), UNIX_TIMESTAMP(UTC_TIMESTAMP())` `1515663090, 1515691890` – sactiw Jan 11 '18 at 09:35
  • @sactiw I cannot reproduce that. What version of MySQL server are you using? [See my results (link here).](https://i.stack.imgur.com/puKx1.png) It doesn't matter what time zone you use, the Unix Timestamp is always the same because as I explained, there's no notion of "timezone" in it beyond the fact that it fixes `0` at `'1970-01-01 00:00:00' UTC`. If you find something to the contrary, it's either an **erroneous buggy implementation** in the software or it could be a mistake in the query. – ADTC Jan 11 '18 at 15:24
  • 1
    I was so used to the UNIX timestamp and now switched to the DateTime object. I stored DateTime objects in MySQL. It took a while before I understood that the timezone of the DateTime object does not get stored in the db. I must keep track of that on the side. When I read the DateTime data back into another page, this page just assumed that it was UTC!!! So I had to start the page with date_default_timezone_set('America/Denver'); (I use GoDaddy server.) I liked the concept of UNIX timestamp more. They are independent of timezone. – cvr Jan 12 '18 at 02:12
  • @ADTC as per your shared result your local timestamp and UTC_timestamp is returning same result which indicates that your server TZ is UTC as well. Try changing the server TZ to something else, say PST, and then run your query again. You will see that unixtimestamp() output is getting affected by the TZ set for the server (TZ:= TimeZone) – sactiw Jan 12 '18 at 07:01
  • 2
    As I understand, UNIX_TIMESTAMP() will always be the same, regardless of the timezone of the server. That is what I used before. But in the example above it is used with an argument UNIX_TIMESTAMP(date) in THIS case the server timezone will be used to interpret the parameter date. – cvr Jan 12 '18 at 10:09
  • @sactiw you are right, with a server that's not set to UTC, what you said is reproducible. However, I want to highlight that this is still a bug on the part of MySQL and **does NOT imply that a Unix timestamp has a time zone**. What's happening is that the _output_ of `UTC_TIMESTAMP()` doesn't have a timestamp attached, so when MySQL gets it as an input for `UNIX_TIMESTAMP(input)`, it interprets the input in the MySQL's timezone setting, hence giving the Unix timestamp for that wrong interpretation. – ADTC Jan 12 '18 at 22:37
  • @sactiw here's a different angle to look at: if you have two MySQL servers set to different time zones (with real difference in offset, and assuming both servers are time-synchronized with NTP), both of them will give you the same value for `UNIX_TIMESTAMP()` when the query is executed in the _same second_ time-frame. I would think that if Unix timestamp had some notion of timezone, it would return different values for different timezones. But that isn't the case. – ADTC Jan 12 '18 at 23:53
  • @ADTC I think two of us are saying the same thing i.e. `unix_timestamp()` function is indeed free of any TZ notion but unix_timestamp() with DATE argument (e.g. `unix_timestamp('2018-01-26 08:58:00')`) will interpret the DATE as per its local time_zone and thus will depend of TZ setting of the server. – sactiw Jan 26 '18 at 09:06
  • @sactiw yep, exactly :) – ADTC Jan 26 '18 at 11:13