Practitioner Level

Working with Dates and Times in MySQL

Pricing
1.25 hours
  • 6 Learning Objectives

About this Hands-on Lab

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.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Log into the MySQL Server as the `root` User and Change to the **prod** Database

Login to the MySQL server:

[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

Run the following statement to insert the dates 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

Use the following statement to insert the times 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

Run the following command to insert the list of dates and times 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

Run the following command to insert the list of dates and times 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;
Contact SalesSee Pricing

Additional Resources

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.

Date and Time Data:

  • Insert the following dates into the date column in the records table: 1989-02-23, 17-12-01, 19851210, 19:06:04.
  • Insert the following times into the time column in the records table: 12:20:45, 08:15, 2247, 11:25:08.778.
  • Insert the following dates and times into the datetime column in the records table: 2015:07:13 04-22-47, 120804100712.
  • Insert the following dates and times into the timestamp column in the records table: 19890905071153, 2004;05;26 02,45,03.
  • Once all the entries have been added, set the time zone to +00:00and list the entries in the records table. Then set the time zone to +03:00 and list the entries again to compare the results.

Additional Information:

  • Tasks in the MySQL database should be performed as the root user (unless otherwise specified).
  • Use the following password to login to the MySQL server as the root user: Linux4me!
  • Do not update the password for the MySQL root user as it is used for grading purposes.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Don't have an account?

Get Started
Who’s going to be learning?
MyselfMy Organization