Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this movie I'll show you how to install time zone support on MySQL on Windows. My SQL supports time zones by using the extensive time zone database maintained by the IANA. That's the internet assigned numbers authority. This database is a standard set of international time zone rules, names and locations. If you're using a Mac or a Unix based My SQL installation, you probably already have this database installed. If you're using Windows, it's probably not installed by default.
In this movie, I'll show you how to install it. This is necessary if you want to learn about the time zone capabilities of MySQL. I'll be using this in the lessons on date and time types and functions. First, let's find out if this machine has time zone support installed. So I'm here in SID. And, I don't need a database selected for this. I'm just going to type in a couple of commands. First is, SHOW VARIABLES, and the word LIKE. And you'll learn, a lot of this SQL syntax later in this, course.
Show variables is a MySQL specific. It's not standard SQL, but it's part of how we manage MySQL. So I've got a single quote and a percent sign and time_zone and another percent sign and a single quote. So this will look for all the variables that have the word timezone with an underscore in the middle in the variables. And then I'm just going to say select now with. Parentheses and a semicolon. And that'll show the current date in whatever time zone is set.
So, I'm going to press Go. And we see our variables with time zone system. Time zone is set to U.S. Mountain Standard Time. That is probably something else on your system, and that's fine, that's what it's supposed to look like. It's just supposed to be set to wherever your system is and then time zone by itself without the word system in front of it. Here's set to system which means it's set to the same as the system time zone, and that is actually not correct. It should say UTC as in universal time coordinated.
That's the standard universal time. And so now shows the time here in U.S. Mountain Standard Time, which is not really what we're looking for here. So, just make sure and to show what the error is. And to be able to confirm that we fixed it after it's fixed. I'm going to type a couple. More commands here. I'm going to say, set time_score. With an underscore like that. Just as it's spell down here. Set timezone equals and in single quotes, I'm going to type US and a slash, and the word eastern with a capital e.
And another single quote and a semicolon. And then I'm going to come and copy and paste these down there. So I have those again. Now, this line here should generate an error if we don't have the time zone database installed. So I'm going to press go. And there's our error. Unknown or incorrect time zone, U.S. Eastern. And that will work after we fix this problem. You see that the time down here is the same as the time up there. And after we fix this problem those will show differently. So, I'm going to go ahead and I'm going to stop my SQL on this system.
So I have the XAMPP Control Panel, and I'm going to stop my SQL. Just press the Stop button there. And then I'm going to come over here, in the browser you notice I have this page loaded up. This is dev.mysql.com/downloads/. Timezones.html and it looks like this. This is where you download these time zone tables from and so just click on this link and download it. I've already downloaded it to my desktop, and so here it is. And it's this zip file so.
I right click on it, and select extract all and just let it extract to the default which will be right on the desktop, and I get this folder here that says, time zone 2011 and pauses. There may be a later version by the time you're doing this and that's fine. But it looks like this has all these files in it. There's three for each table, FRM, MYD, and MYI for. Each table. And there are looks like five tables here. Just going to select all of this.
And we're going to copy it into the MySQL database folder. So here I have C:/zampp/ open. And I'm going to open under that MySQL. And data and then my SQL and this is the my SQL system tables and if you scroll down here you may or may not see some time zone tables. These may be here these may not be here. In this case they are here and you'll notice that their size is very very small.
That just means they're place holders. That's find we're going to copy these new ones over them. And I'm just going to arrange these windows here to make this easy to do. And so I'm holding down the control key as I copy them. And I'm going to grab them over here it says copy to MySQL and replace the files in the destination. And that should do it. That should actually be all that's necessary to install these timezone tables. So now I start MySQL again. And I come back over here to the browser and come back over to SID, and I already have this command in here.
If I just press Go, this should just work. And there it is. The error is gone, and you notice in our results here. You notice UTC, there in the first query. That means that SID was able to initialize it's own time zone. Before, it couldn't because UTC wasn't in the time zone tables. And you notice that after we set time zone equals US eastern, now it says US eastern. And that this time and this time are not the same. Of course if you're in the eastern time zone, you may have already had.
Eastern. You can always set this to Pacific. You can type Pacific like this. Course these two times won't be the same because we have UTC now for that one. But, I press go here and now we have Pacific down here. So you can try them both and these times'll be different. This one is UTC. And this one is in Pacific now or Eastern if we type Eastern here. So, you have now successfully installed the MySQL timezone database on your Windows system.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 92784 Viewers
82 Video lessons · 104216 Viewers
71 Video lessons · 75887 Viewers
56 Video lessons · 107257 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.