Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Mysql provides time zone support on a number of different levels, for most purposes you'll only be interested in the session time zone setting. Please note that this lesson requires time zone support is installed on your system. If you have not installed time zone support for your system, the exercises in this lesson will not work. For this lesson, we're not going to be selecting a database, we're going to be setting some variables and using time stamps. So for example, we're just going to start with show variables like, and in single quotes %time_zone% single quote and semi-colon, and this will show us the time zone related variables in the system.
You notice that the system time zone is set to the time zone of this machine, which is in Mountain Standard Time, and the time zone variable itself is set to UTC. Now if you don't see UTC there, if you see system or something else, it probably means that you do not have times and support installed on your system. SID sets this variable to UTC, so it should say UTC here. That time zone variable times zone just by itself is this session variable. So if I say SELECT NOW, and then I set that variable to something else, so SET time_zone = and U.S./Eastern..
We'll talk about that in a moment. Just set it to that for now. And then we'll do another select now. Actually I'm going to Copy and Paste both of these. We'll show the variables again, and we'll SELECT NOW again. And so we've got all of these. Show the variables, SELECT NOW, SET the time zone and then show variables and SELECT NOW again. And you see that our first now is the UTC now. And, then Query 4, because Query 3 sets the time zone variable.
Query 4 shows our time zone as now set to US Eastern, and we have a different time that's a few hours earlier. So this time zone setting here that says US/Eastern, that's in the format of the IANA time zone database. It's a standardized list of time zones around the world. You can see a full list of them on the associated Wikipedia page here. Just search for TZ database time zones in Wikipedia, and you should find it.
And you'll notice that this is a very complete list, it's organized by region, and city or zone. So you got all these Africa all these America ones, and way down here towards the bottom you'll see the US related ones. And so US Alaska which has its own time zone, US Arizona which has its own time zone, US Central, US Eastern, and US Mountain and Pacific. So if I come back here and I set this to US Pacific, I will get a different result.
So instead of it saying 22:29, it should be three hours earlier, and there it is, 19:31, because it's been a couple of minutes since I did this before. So this is how timezone support in MySQL works. MySQL provides timezone support using the IANA time zone database. For most purposes you'll only be interested in the session time zone setting, which is this variable here.
Get unlimited access to all courses for just $25/month.Become a member