In this session, you will be learning how to work with dates and times using SQL, along with some helpful examples and tables.
Date and Time Manipulation
In the database you mostly need to store data and time values along with strings and numbers, for example, a user’s birth date, employees hiring date, date of the future programs and events, the date and time created and modified a specific row in a table, and so on.
This kind of data is considered as temporal data and have a default storage format and data types of storing them in the database engine. The mention below table will show the data types who will support the MySQL database server for handling the dates and times.
DATE YYYY-MM-DD 1000-01-01 to 9999-12-31 TIME HH:MM:SS or HHH:MM:SS -838:59:59 to 838:59:59 DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59 TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 to 2037-12-31 23:59:59 YEAR YYYY 1901 to 2155
The mention below statement will show how to insert a date in the database table.
INSERT INTO employees (emp_name, hire_date, salary)
VALUES ('Adam Smith', '2015-06-24', 4500);
Tracking Row Creating or Modification Times
You mostly needed to shore record creation time or last update modification time in your database when you are working with the database of an extensive application, for example, when a user logs In, or when the user last updated his password, etc.
In MySQL , you can use the function NOW( )
INSERT INTO users (name, birth_date, created_at)
VALUES ('Bilbo Baggins', '1998-04-16', NOW());
You can also auto-update by using auto-initialization and auto=update properties of the TIMESTAMP DATETIME
Extracting Parts of Dates or Times
When you are working, there may be some situations where you want to be a part of the date or time only. In MySQL, you have to design the special functions for extracting part of a temporal value as shown.
YEAR(), MONTH(), DAYOFMONTH(), MONTH NAME(), DAY NAME(), HOUR(), MINUTE(), SECOND(), etc.
The mention SQL statement will extract the year part of the birthdate columns; for example, if the birthdate of any user is 1987-01-14, the YEAR(birth_date) will return 1987.
mysql> SELECT name, YEAR(birth_date) FROM users;
To get the day of the month, you can use the DAYIFMONTH( ) function. for example, if the Birth_date of any user is 1986-10-06, the DAYOFMONTH(birth_date) will Return 6.
mysql> SELECT name, DAYOFMONTH(birth_date) FROM users;
Formatting Dates or Times
If you want the date format more readable and prominent in your result, you can use the function DATE_FORMAT() TIME_FORMAT()
mysql> SELECT name, DATE_FORMAT(birth_date, '%M %e, %Y') FROM users;
