Normally what you want to do is always store the data in UTC time in the database, and then translate UTC time to whatever local time zone you want when you need to do so, in the client code. Monday, May 21, 2018 6:20 PM. To explicitly specify the system time zone for MySQL Server at startup, set the TZ environment variable before you start mysqld. If you start the server using mysqldsafe, its -timezone option provides another way to set the system time zone. The permissible values for TZ and -timezone are system dependent. For example, convert UTC to MET (Middle European Time). SELECT CONVERTTZ('2004-01-01 12:00:00', 'UTC', 'MET'); Tips/Notes #1) To get the list of all timezones in MySQL system database, you can run the below query and use any of the timezone names for converting from one timezone to another. Select. from mysql.timezonename. Per-connection time zones. Each client that connects has its own time zone setting, given by the session timezone variable. Initially, the session variable takes its value from the global.
unfortunately, as you are probably aware, timezones— Gabor Javorszky (@javorszky) June 1, 2016
Wow, holy shit, grab a seat, I am going to lead you down the rabbit hole! There are some very nuanced things in how MySQL (and PHP, by the way) handles time zones, and some of it was completely baffling and new to me, and most of it absolutely blew my mind, so to contribute to world peace in the form of saving you from going ballistic due to the computer not doing what you think it should be doing, let me share my new found knowledge.
Fig1. My mind. 50% frustration, 50% wtf, 20% this is neat!
We’re dealing with dates and times, and would love to store them against other records in our MySQL database, and use them in our application in PHP. It would also be nice if we could query by them in a fast and efficient manner.
To set up some more things, there are five time zones you need to deal with at the same time, and three of them are settings on your server:
- your ACTUAL time zone. I live in Oxford, so my timezone right now is BST, or UTC+1
- your server’s timezone. I have a Digital Ocean box in San Francisco. That’s pacific time, so UTC-7 at the moment.
- your mysql’s timezone on the server. That may or may not be the same as the system’s. Usually it is.
- your mysql connection’s timezone. Because you can dynamically set it per connection. Aren’t timezones neat?
- your php’s timezone. Again, it is usually your system’s, but it might not be.
With that, it would be nice to store dates in a way that we could get back the stored value half a year / year later, after a move to the other side of the world, and it meant the same thing. But .. what should we store them in?
Okay, the most straightforward way to store a date and time is in a
TIMESTAMP data type. It has the format of
YYYY-MM-DD HH:MM:SS, and has a few neat tricks up its sleeve.
It does date validation! Meaning you can’t store 30th February into it, it’ll store
0000-00-00 00:00:00 instead.
Internally it stores the date as UTC. Which means that the actual value stored will largely depend on your current
time_zone setting. If I’m trying to store
2016-06-01 23:52:17 right now, it will store it as
2016-06-01 22:52:17, because that’s the time in UTC. If I were to set the
time_zone to something else, like
SET time_zone = '+8:00', then saving the same
2016-06-01 23:52:17 date will result in an internal datetime of
Because of the above, it follows your timezone changes. Look at this:
I have a table. Column
ts is a
DATETIME type. I start with being in UTC+1, I insert a record, change to UTC+8, insert another record (the current time both times), change back to UTC+1, and look at what’s happened.
As you can see even though I inserted the current time (23:59) in two vastly different timezones, the
TIMESTAMP value ends up being the same because, well, UTC.
The implication however is that if you insert a certain value in timezone 1 and then change timezones, and you expect the same value to come out in timezone 2, it won’t.
We have an assumption that the dates stored are always in UTC, so from this regard
TIMESTAMP is absolutely rubbish.
On top of that, because it’s storing it as UTC in the background as a unix timestamp, and THAT has a limitation because it’s a signed 32 bit number, the minimum date is 1970-01-01 00:00:00 and the maximum is 2038-01-19 03:14:07 UTC (that’s 2^31-1), so you can’t really store things like birthdays of mortgage dates (to keep the examples diverse ;) ).
Also as a related function,
FROM_UNIXTIME is also dependent on the current time zone. See this:
TIMESTAMP is great if you want to store “points in time”, for example things like
updated_at. That happens at one point, and if you move time zones, the point stays the same because the value will also change. The drawback however is that if you assume that the
updated_at is also always in UTC. In that case you’re out of luck again.
And then you also can’t really trust that your site’s timezone is going to be the same as PHP’s timezone, or MySQL’s timezone. All is fun.
One more super fun thing. Say you have a
DATETIME field, and you want to convert that into a
TIMESTAMP field. The current value of
DATETIME will be used as the local timezone variant when determining the internal value of the new
TIMESTAMP field as described above.
More important than that though, is that this:
but only if the new column would be the very first
TIMESTAMP column in that table, because tables can only have one and only one
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP field (unless you’re running a very up to date MySQL instance, in which case you might be able to achieve this with more than one fields..).
Aren’t databases fantastic? So if you want to avoid that, you need to explicitly tell it not to do that:
It looks a lot like
TIMESTAMP, but is very different. First of all, it has the same structure:
YYYY-MM-DD HH:mm:ss, but then the documentation says this:
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
If you pass in 0 as
DATETIME, you will get this:
That's because 0 is treated as false. Null becomes null, and any int becomes null due to them being not valid datetime values.
Interestingly enough even though the documentation says the minimum should be the year 1000, I have successfully cast the year 800 onto it:
This type, as opposed to
TIMESTAMP, does not hold timezone information, so what you put in is what you’re going to get out, regardless of timezones. See the example about timezone changes previously.
The good thing here is that if your code assumes that all time information stored in the database is in UTC, this is perfect. That however means you need to make sure your code passes in UTC timezoned string, and when it reads back it will turn it back into whatever normal timezone the site / application has.
If you store a datetime as
YYYY-MM-DD HH:mm:ss, you might as well do it in a unix timestamp. The good thing about it is that it’s so much faster to query by than a piece of string. The bad news is that the data stored in the database will be less human-friendly. When you look at something like
2016-06-06 22:21:49, you know immediately what it means, but if you look at
1465251709, you have no idea. Not even a mild inkling of where that might be in relation to 'now'. The two are the same, by the way.
On the other hand, I argue that once you’re at the point where you need performance where this matters, then the database stops being something to look at for humans. Go and build pretty interfaces for it, and make sure your code can handle converting between formats and timezones.
How to tell what my timezone offset of the database is?
It’s surprisingly hard to find that information out. Involves reading settings which you may or may not have access to:
Well, gee, thanks a bunch.. -.- I have no idea what
SYSTEM is unless I have access to the server itself.
Do this instead:
It means my server is in UTC+1 at the moment.
Hope I shed some light on time stuff. To recap:
What should I store my dates in?
- Can your application supply / read / assume that all dates are UTC, and you want performance? ->
INTEGERas a unix timestamp.
- Can your application supply / read / assume that all dates are UTC, or you need to store dates older than 1970 or after 2038, but want them to be human readable? =>
- Do you need a fixed point in time regardless of timezones, and will your code assume the value you get is in the local timezone of the MySQL server? =>
Chrome beta canary. As always, if you have questions, reach out to me on twitter. Or write me an email.
If you plan to work with date and time data in PostgreSQL, you’ll want to ensure that you’ve set the correct time zone for your location. Fortunately, it’s easy to make this change using the
psql command-line interface. In this article, we’ll explain how to connect to PostgreSQL and set the time zone.
Before we proceed with our tutorial, there are a couple of important prerequisites that need to be in place. First, you’ll need to make sure that PostgreSQL is already installed on your system. You’ll also need to have
psql, the command-line interface for PostgreSQL, installed. To check if PostgreSQL is installed, you can use the
psql -V or
postgres -V commands. Either of these commands will return the number of the version that you have installed.
Use psql to connect to a database
The first thing we’ll need to do is enter the
psql interface. If you try to run the
psql command by itself, it will attempt to connect to the interface using the default
postgres admin role. If this happens, you might see an error stating:
FATAL: role 'postgres' does not exist; if this occurs, you’ll have to switch to the
postgres user in your terminal using the command
sudo -u postgres -i and create one.
Once you have a Postgres user set up with role attributes, you can connect to a specific database in psql using the ocmmand shown below:
psql -U objectrocket -d some_db
NOTE: Be sure to replace
some_db with the appropriate values for your username and database.
Now that we’re connected to a PostgreSQL database, we can start working with time zones. Use the following command in the
psql interface to have Postgres return your current time zone:
You’ll see something like the following:
some_db=# SHOW TIMEZONE;
Postgres time zone list
If you’re not sure of the exact name of the time zone you want to set, you can use the following command to get the complete list of all PosgreSQL time zones:
There are a few accepted formats and column names that can be used to reference time zones: the zone names (
name), the abbreviated names (
abbrev), and the UTC hourly offset integers (
pg_timezone_names table also has a
is_dst column that indicates if a zone is currently in Daylight Savings Time.
Use the following SQL statement to have psql return just the
name column from the time zone table:
SELECT name FROM pg_timezone_names;
Set the PostgreSQL time zone
Before we attempt to use the
NOW() function, it’s important to confirm that the time zone is set properly:
If the string value used in the
SET TIMEZONE command is invalid, Postgres will default to the current UTC (Coordinated Universal Time) time.
Set the Postgres time zone with an integer
You can also set the time zone using an integer value representing the hourly difference from UTC time. You can pass the value as a string or as an integer:
If you use an integer to set the time zone, the
SHOW TIMEZONE command will return the UTC integer value instead of the string name for the zone:
some_db=# SHOW TIMEZONE;
Set the Postgres time zone with an abbreviated name
Although PostgreSQL offers the option of setting a time zone with an abbreviated name,
UTC are the only abbreviated names you can use. The following example shows how you can reset your Postgres database to Greenwich Mean Time (GMT), which is equivalent to UTC:
Get a timestamp of the local time zone
You can use the
'localtime' string to get a ISO 8601 compliant timestamp that utilizes the set time zone:
SELECT LOCALTIMESTAMP AT TIME ZONE 'localtime';
Postgres timestamps will display the set time zones by default, but there are ways you can modify the timestamp so that it’s not impacted by the time zone set by Postgres. You can also omit the time zone from your timestamps.
Postgres timestamp without time zone
We can use the SQL command shown below to have psql return a timestamp without the time zone at the end:
Default Postgres time zone
Keep in mind that the
TIMEZONE value is a session-only variable– this means that it resets to the default value every time you exit and re-enter the psql command-line interface.
Change Mysql Timezone
Permanently set the Postgres time zone
Mysql Set Timezone To Utc
If you’d like to permanently change the default PostgreSQL time zone for all of your databases, you’ll need to modify the
postgresql.conf file. Linux users will likely find the file in the
/etc/postgresql directory. To locate this file, change into the directory for your PostgreSQL version and then into the
After you’ve made the necessary changes, don’t forget to restart the PostgreSQL service:
Mysql Set Timezone To Utc Google
sudo systemctl restart postgresql.service
Mysql Check Timezone
Setting the correct time zone is important when you’re working with date and time values in PostgreSQL. It’s easy to connect to PostgreSQL and set the time zone with just a simple command. In this article, we showed you how to set the time zone and how to display a timestamp both with and without the current time zone. Using the examples we provided, you’ll be able to manage the time zone setting on your own installation of PostgreSQL.