MySQL timestamp timezone handling

Question

Does the timestamp data type represent an instant-in-time or a year-month-date-hour-minute-second value? An instant in time is a moment, like the moment you started reading this, and it can be described with many different y-m-d-h-m-s+timezone values. Without timezone, it's ambiguous, the same moment can be 17:25 for me and 13:25 for someone else.

Here's what I think I get. The current_timestamp function returns the current time in the current timezone, so this is an unambiguous instant, the real now. Inside a table, it is stored as UTC, so with this assumed timezone, it should also be unambiguous.

Now here's what I don't get: it's the existence of the utc_timestamp function. I stored a current_timestamp and an utc_timestamp inside the same column, and they were different. This function shouldn't even exist, I don't understand why it represents a different instant in time.

Answer 1

TIMESTAMP stores the number of seconds from 1970年01月01日 00:00:01 to now. It automatically converts to the date and time format when you retrieve the data.

CURRENT_TIMESTAMP(): Returns the current date time with your timezone configured. UTC_TIMESTAMP(): Returns the current date and time using UTC timezone.

If you're using a timezone that's not UTC, these functions always return different values, equal otherwise.

MySQL always stores the date and time in UTC so it can be easily converted to different timezones. NOW() or CURRENT_TIMESTAMP() return the date/time plus your timezone configured.

UTC_TIMESTAMP() just returns the date/time ignoring your timezone setting.

• fejesjoco: The documentation says this about the timestamp type: it is always stored as UTC and it is always retrieved as the current timezone. This leaves no space for two different timestamp functions. The current time is an exact value, it cannot be stored in two different ways inside a timestamp column.
• Erico: I didn't say the value can be stored in two different ways. Each function returns the data in different ways. Using the timezone or not.
• fejesjoco: Both functions should return the current date and time, but the timestamp column only supports one timezone (an implicit one). If you store the results of both functions in a timestamp column, you will get two different values, two different instants in time. So one of them was not "now", not the "current" time.
• sactiw: I think a very important statement about UTC_Timestamp() is that it will be same across all servers in various time-zones as long as those servers are set to correct time as per their own timezone.

Answer 2

If you use current_timestamp, MySQL will use timezone which is configured in my.cnf. If no value is set in my.cnf, then MySQL will determine your operating systems' timezone and use that upon startup.

You can see how your server is configured with this query:

mysql> SELECT @@global.time_zone, @@session.time_zone;

Also, it is possible to set timestamp per session.

utc_timestamp is used to always get timestamp in UTC, no matter what MySQL server's timestamp is configured to.

• fejesjoco: The documentation says this about the timestamp type: it is always stored as UTC and it is always retrieved as the current timezone. This leaves no space for two different timestamp functions. The current time is an exact value, it cannot be stored in two different ways inside a timestamp column.
• ek9: Yes but let's say you connect to MySQL session and configure it to use GMT+2. Now you want to insert new row in a query, where time in one column should be in GMT+2, but in the other - UTC. By using current_timestamp() and utc_timestamp() you can do this easily in one query with simple SQL.
• fejesjoco: Problem is, I can't tell MySQL to store one timezone in one column and another timezone in another column. It uses a fixed, implied timezone. Of course I could do timezone calculations myself, but then again, there's no point in using utc_timestamp() if I can format my regular timestamp as anything. The main problem is that utc_timestamp() represents a different instant in time in the context of a timestamp column.
• Anshul: In short, current_timestamp() and utc_timestamp() both are stored (and retrieved) as exact same value in a timestamp column, given both are exectued at exact same time.