Working with Dates and Times in MySQL

Hands-On Lab

 

Photo of Matthew Pearson

Matthew Pearson

Linux Training Architect II in Content

Length

01:15:00

Difficulty

Intermediate

MySQL allows for dates and times to be entered in a "relaxed" format, and then translates those entries into a standardized format. In this lab, we will be working with the date, time, datetime, and timestamp datatypes and then show how MySQL handles each one.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Working with Dates and Times in MySQL

The Scenario

Our team has been supplied with a list of dates and times that need to be entered into the records table in the prod database.

Some additional Information:

  • The root MySQL user's password is: Linux4me!.
  • Do not update the password for the MySQL root user, as it is used for grading purposes.

Getting Logged In

Use the credentials and server IP in the hands-on lab overview page to log into our lab server. Refer back to that page for other usernames and passwords as we need them. Once we're logged into the server itself, log into MySQL as root:

[cloud_user@host]$ mysql -u root -p

Change to the prod database:

mysql> USE prod;

Insert the List of Dates Provided in the Instructions into the date Column

mysql> INSERT INTO records (date) VALUES ('1989-02-23'), ('17-12-01'), ('19851210'), ('19:06:04');

Insert the List of Times Provided in the Instructions into the time Column

mysql> INSERT INTO records (time) VALUES ('12:20:45'), ('08:15'), (2247), ('11:25:08.778');

Insert the List of Dates and Times Provided in the Instructions into the datetime Column

mysql> INSERT INTO records (datetime) VALUES ('2015:07:13 04-22-47'), (120804100712);

Insert the List of Dates and Times Provided in the Instructions into the timestamp Column

mysql> INSERT INTO records (timestamp) VALUES ('19890905071153'), ('2004;05;26 02,45,03');

After Setting a Baseline Timezone of +00:00, Update the Time Zone by +03:00 Hours and See Its Effect on the datetime and timestamp Columns

Establish a baseline timezone:

msyql> set time_zone = '+00:00';

List the data in the records table prior to updating the timezone to +03:00:

mysql> select * from records;

Update the timezone by +03:00 hours:

msyql> set time_zone = '+03:00';

List the data in the records table and notice the change to the timestamp column:

mysql> select * from records;

Conclusion

Does anybody really know what time it is? Does anybody really care? Just kidding; excuse the Chicago humor. Good job though, on all of those date and time queries. Congratulations on getting them done.