New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Time zones in MySQL

From: MySQL Essential Training

Video: Time zones in MySQL

Mysql provides time zone support on a number of different levels, for If you have not installed time zone support for and in single quotes %time_zone% single quote and And so we've got all of these.

Time zones in MySQL

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.

Show transcript

This video is part of

Image for MySQL Essential Training
MySQL Essential Training

60 video lessons · 4649 viewers

Bill Weinman
Author

 
Expand all | Collapse all
  1. 4m 22s
    1. Welcome
      1m 3s
    2. Using the exercise files
      1m 31s
    3. What is MySQL?
      1m 48s
  2. 45m 37s
    1. Installation overview
      3m 16s
    2. Installing XAMPP on Windows
      5m 55s
    3. Installing XAMPP on the Mac
      6m 38s
    4. Setting up MySQL users
      11m 31s
    5. Installing SID on Windows
      5m 43s
    6. Installing SID on the Mac
      6m 6s
    7. Installing time zone support in MySQL on Windows
      6m 28s
  3. 45m 43s
    1. The SELECT statement
      3m 57s
    2. Selecting rows
      4m 57s
    3. Selecting columns
      3m 8s
    4. Sorting results with ORDER BY
      2m 58s
    5. Filtering results with WHERE
      3m 52s
    6. Filtering results with LIKE and IN
      3m 41s
    7. Filtering results with regular expressions
      8m 21s
    8. Inserting rows
      4m 9s
    9. Updating rows
      2m 21s
    10. Deleting rows
      2m 25s
    11. Literal strings
      3m 12s
    12. Understanding NULL
      2m 42s
  4. 41m 47s
    1. Creating a database
      4m 30s
    2. Creating a table
      7m 18s
    3. Creating indexes
      6m 8s
    4. Controlling column behavior with constraints
      4m 46s
    5. Creating an ID column
      6m 58s
    6. Using foreign key constraints
      7m 58s
    7. Altering a table
      4m 9s
  5. 28m 56s
    1. What are data types?
      4m 1s
    2. Numeric types
      5m 21s
    3. String types
      2m 58s
    4. Date and time types
      7m 2s
    5. Bit type
      2m 26s
    6. Boolean values
      2m 15s
    7. Enumeration types
      4m 53s
  6. 32m 34s
    1. String functions
      6m 57s
    2. Numeric functions
      6m 2s
    3. Date and time functions
      4m 12s
    4. Time zones in MySQL
      3m 37s
    5. Formatting dates
      1m 51s
    6. Aggregate functions
      5m 45s
    7. Flow control with CASE
      4m 10s
  7. 7m 6s
    1. Maintaining database integrity with transactions
      4m 46s
    2. Using transactions for performance
      2m 20s
  8. 16m 49s
    1. Updating a table with a trigger
      5m 11s
    2. Preventing automatic updates with a trigger
      7m 29s
    3. Logging transactions with a trigger
      4m 9s
  9. 14m 11s
    1. Creating a simple subselect
      3m 23s
    2. Searching within a result set
      3m 53s
    3. Creating a view
      3m 32s
    4. Creating a joined view
      3m 23s
  10. 12m 26s
    1. Understanding MySQL stored routines
      2m 0s
    2. Creating a stored function
      4m 34s
    3. Creating a stored procedure
      5m 52s
  11. 14m 4s
    1. The multi-platform PDO interface
      3m 44s
    2. Executing the SQL
      4m 8s
    3. Implementing auto-increment IDs
      2m 3s
    4. Using a stored funciton
      4m 9s
  12. 1m 3s
    1. Goodbye
      1m 3s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.