Our Special Offer - Get 3 Courses at 24,999/- Only. Read more
Hire Talent (HR):+91-9707 240 250

General

SQL DateTime

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

Idceated_at
1December 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)
201941250181054

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:

DtDATEDIFF(NOW(),dt)
August 12, 2019129
May 13, 2019220
May 13, 2019220
May 24, 2019209
July 24, 2019148
April 17, 2019246
April 10, 2019253

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.

Related Blogs

  1. SQL Joins
  2. Schema in SQL
  3. Decode in SQL
Besant Technologies WhatsApp