SQL DateTime
Introduction
Dealing with time and date in SQL sometimes is much tricky. As all, we know time and date are different data types completely, but often they are combined as DateTime data types. SQL dates and times are simple but combining them can be tedious. In this tutorial, we will see all about SQL DATETIME data type.
- DATETIME data type – An overview
- MySQL DATETIME functions
- MySQL DATE function
- MySQL TIME function
- MySQL DATE_FORMAT function
- MySQL DATE_ADD function
- MySQL DATE_SUB function
- MySQL DATE_DIFF function
What do you mean by the DateTime data type?
They are the numbers or values that hold both date and time. SQL has different data types that merge both the time and data specifications making things more tedious. The most commonly used is the DATETIME as it present in every SQL early version.
SQL gets and displays DateTime values in the format of
‘YYYY-MM-DD HH: MM: SS’.
The default range supported in DATETIME is starting from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
A DATE value makes use of 5 bytes for the purpose of storage. Apart from that, DATETIME value holds another range of fractional second up to microseconds. The format for this is
YYYY-MM-DD HH:MM:SS[.fraction]
Let me explain with a clear example.
2019-12-18 11:03:00.999999
DATETIME values is in need for more storage when they include a fractional second precision. Let’s check them below.
- 0 fractional seconds precision – 0 storage bytes
- 1,2 fractional seconds precision – 1 storage bytes
- 3,4 fractional seconds precision – 2 storage bytes
- 5,6 fractional seconds precision – 3 storage bytes
Let me explain this with an example.
2019-12-18 11:03:00.999999 needs a total of 8 bytes. 3 bytes for .999999 and 5 bytes for 2019-12-18 11:03:00, whereas 2019-12-18 11:03:00.9 needs only 6 bytes, here 1 byte is for the fractional second precision.
MySQL DATETIME functions:
Dt represents the variable for the date and Now() function for the time.
Example
Set @dt=Now(); The SELECT statement is used for querying the @dt value. SELECT @dt;
Output
@dt 2019-12-18 04:05:10
MySQL Date function:
The DATE function is used to extract the DATETIME value’s date portion.
Example
SELECT DATE(@dt);
Output
DATE (@dt) 2019-12-18
The function is used in the case if you need to query any data depending on the date but the information stored in the column depends on both time and date.
Let’s check out an example.
CREATE TABLE test_dt (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at DATETIME
);
INSERT INTO test_dt(created_at)
VALUES('2019-12-18 04:05:10');
You can make use of the following query if you need to know the exact row create on 2019-12-18
SELECT*FROM test_dtWHERE created_at = '2019-12-18';
In this case, there is no row returned. It is due to the column of created_at holds both date and time. If you need to make it right, you can make use of the DATE functions as below.
Input
SELECT * FROM test_dt WHERE DATE(created_at) = '2019-12-18';
Output
| Id | ceated_at |
| 1 | December 18, 2019 |
From the above example, you can see the function has returned one row as expected. Let’s take, if the table holds many rows, in this case, MySQL is required to perform a full table scan in order to point out the rows which match the requirements.
MySQL TIME function:
You can make use of the TIME function if you need to extract the portion of time from the value of DATETIME.
Input
SELECT TIME(@dt)
Output
| TIME(@dt) |
| January 1, 1970 |
MySQL SECOND, MINUTE, HOUR,DAY,WEEK, MONTH,QUARTER, and YEAR functions:
You can make use of the following functions in order to get the second, minute, hour, day, week, month, quarter, and year from a DATETIME value.
Input
SELECT YEAR(@dt) QUARTER(@dt) MONTH(@dt) WEEK(@dt) DAY(@dt) SECOND(@dt) MINUTE(@dt) HOUR(@dt)
Output
| YEAR(@dt) | QUARTER(@dt) | MONTH(@dt) | WEEK(@dt) | DAY(@dt) | SECOND(@dt) | MINUTE(@dt) | HOUR(@dt) |
| 2019 | 4 | 12 | 50 | 18 | 10 | 5 | 4 |
MySQL DATE_FORMAT function:
You can make use of the DATE_FORMAT function to format the value of DATETIME. Let’s consider an example.
Below is the format(%H:%i:%s – %W %M %Y) of DATETIME value.
Input
SELECT DATE_FORMAT(@dt, ‘%H:%i:%s - %W %M %Y’);
Output
| DATE_FORMAT(@dt, ‘%H:%i:%s - %W %M %Y’) |
| 04:05:10 - Wednesday, December 2019 |
MySQL DATE_ADD function:
The function DATE_ADD is used to add an interval to the value of DATETIME.
SELECT @dt start, DATE_ADD(@dt, INTERVAL 1 YEAR) '1 year later', DATE_ADD(@dt, INTERVAL 1 MONTH) '1 month later', DATE_ADD(@dt, INTERVAL 1 WEEK) '1 week later', DATE_ADD(@dt, INTERVAL 1 DAY) '1 day later', DATE_ADD(@dt, INTERVAL 1 HOUR) '1 hour later', DATE_ADD(@dt, INTERVAL 1 MINUTE) '1 minute later', DATE_ADD(@dt, INTERVAL 1 SECOND) '1 second later';
MySQL DATE_SUB function:
The function DATE_SUB is used to subtract an interval to the value of DATETIME.
SELECT @dt start, DATE_SUB(@dt, INTERVAL 1 YEAR) '1 year before', DATE_SUB(@dt, INTERVAL 1 MONTH) '1 month before', DATE_SUB(@dt, INTERVAL 1 WEEK) '1 week before', DATE_SUB(@dt, INTERVAL 1 DAY) '1 day before', DATE_SUB(@dt, INTERVAL 1 HOUR) '1 hour before', DATE_SUB(@dt, INTERVAL 1 MINUTE) '1 minute before', DATE_SUB(@dt, INTERVAL 1 SECOND) '1 second before';
MySQL DATE_DIFF function:
The function DATEDIFF is used to calculate the variation between the two values of DATETIME. This function always takes the part of date from DATETIME value in their calculation.
Let’s check out the following example.
We are now going to create a table in the name of datediff_click. It includes one column and the data type for the column is DATETIME. The syntax will look like
CREATE TABLE datediff_click( dt DATETIME);
Secondly, we are going to add some rows in the table of dateddiff_click.
INSERT INTO datediff_click(dt)
VALUES('2019-08-12 13:55:5'),
('2019-03-16 04:44:38'),
('2019-05-13 03:26:56'),
('2019-05-24 14:17:16'),
('2019-07-24 01:19:10'),
('2019-04-17 22:52:21'),
('2019-04-10 07:27:39');
The third step is to make use of the DATEDIFF function to compare the current time and date with each two value in the datediff_click table.
Input
SELECT dt,DATEDIFF(NOW(), dt) FROM datediff_click;
Output:
| Dt | DATEDIFF(NOW(),dt) |
| August 12, 2019 | 129 |
| May 13, 2019 | 220 |
| May 13, 2019 | 220 |
| May 24, 2019 | 209 |
| July 24, 2019 | 148 |
| April 17, 2019 | 246 |
| April 10, 2019 | 253 |
I hope you are clear on the SQL DATETIME data type. Any other queries related to SQL DATETIME? Let us know through the comment section below.
