MySQL - SYSDATE() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL SYSDATE() function is used to get the current date and time value. The resultant value is a string or a numerical value based on the context and, the value returned will be in the 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format.

This function is similar to the Now() function but, SYSDATE() returns the time at which it executes and NOW() returns the time it began to execute. i.e. if you use NOW() within a stored function it returns the start of execution time of the stored procedure.

Syntax

Following is the syntax of the above function –

SYSDATE([fsp]);

Example 1

Following example demonstrates the usage of the SYSDATE() function –

mysql> SELECT SYSDATE ();
+---------------------+
| SYSDATE ()          |
+---------------------+
| 2021-07-10 22:11:24 |
+---------------------+
1 row in set (0.00 sec)

Example 2

Following is an example of this function in numerical context –

mysql> SELECT SYSDATE()+0;
+-----------------------+
| SYSDATE()+0           |
+-----------------------+
| 20210710221124        |
+-----------------------+
1 row in set (0.00 sec)

Example 3

You can add seconds to the current time stamp as shown below –

mysql> SELECT SYSDATE()+12;
+------------------------+
| SYSDATE()+12           |
+------------------------+
| 20210710221136         |
+------------------------+
1 row in set (0.00 sec)

Example 4

In the following example you can observe the difference between NOW() and SYSDATE() –

mysql> SELECT NOW(), SLEEP(8), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(8) |               NOW() |
+---------------------+----------+---------------------+
| 2021-07-15 15:35:17 |        0 | 2021-07-15 15:35:17 |
+---------------------+----------+---------------------+
1 row in set (8.07 sec)

mysql> SELECT SYSDATE(), SLEEP(8), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(8) |           SYSDATE() |
+---------------------+----------+---------------------+
| 2021-07-15 15:35:46 |        0 | 2021-07-15 15:35:54 |
+---------------------+----------+---------------------+
1 row in set (8.01 sec)

Example 5

We can also subtract the desired number of seconds from the current time using this function –

mysql> SELECT SYSDATE()-12;
+-------------------+
| SYSDATE()-12      |
+-------------------+
| 20210710221112    |
+-------------------+
1 row in set (0.00 sec)

Example 6

This function accepts an optional argument i.e. fsp, using this you can specify the number of digits you need after the fraction for seconds.

mysql> SELECT SYSDATE(3);
+-------------------------+
| SYSDATE(3)              |
+-------------------------+
| 2021-07-11 18:32:10.526 |
+-------------------------+
1 row in set (0.00 sec)

Example 7

Suppose we have created a table named dispatches_data with 5 records in it using the following queries –

mysql> CREATE TABLE dispatches_data(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp timestamp,
	Price INT,
	Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');

Following query is another example of this function —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, TIMESTAMPDIFF (MINUTE, DispatchTimeStamp, SYSDATE()) as Difference FROM dispatches_data;
+-------------+--------------+---------------------+-------+------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | Difference |
+-------------+--------------+---------------------+-------+------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  | 1149590    |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  | 1073319    |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  | 837542     |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  | 1738492    |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  | 1329843    |
+-------------+--------------+---------------------+-------+------------+
5 rows in set (0.00 sec)

Example 8

Suppose we have created a table named SubscriberDetails with 5 records in it using the following queries –

mysql> CREATE TABLE SubscriberDetails (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionTimeStamp timestamp
);
insert into SubscriberDetails values('Raja', 'Premium', TimeStamp('2020-10-21 20:53:49'));
insert into SubscriberDetails values('Roja', 'Basic', TimeStamp('2020-11-26 10:13:19'));
insert into SubscriberDetails values('Puja', 'Moderate', TimeStamp('2021-03-07 05:43:20'));
insert into SubscriberDetails values('Vanaja', 'Basic', TimeStamp('2021-02-21 16:36:39'));
insert into SubscriberDetails values('Jalaja', 'Premium', TimeStamp('2021-01-30 12:45:45'));

Following query calculates and displays the remaining number of days for the subscription to complete —

mysql> SELECT SubscriberName, PackageName, TIMESTAMPDIFF(DAY, SubscriptionTimeStamp, SYSDATE()) as RemainingDays FROM SubscriberDetails;
+----------------+-------------+---------------+
| SubscriberName | PackageName | RemainingDays |
+----------------+-------------+---------------+
| Raja           | Premium     |           262 |
| Roja           | Basic       |           226 |
| Puja           | Moderate    |           125 |
| Vanaja         | Basic       |           139 |
| Jalaja         | Premium     |           161 |
+----------------+-------------+---------------+
5 rows in set (0.00 sec)