SQL now

Introduction to SQL now

NOW() function is a date and time function in standard query language(SQL) that is primarily used to get the current date and timestamp based on the server/system settings. The function returns a string in the ‘YYYY-MM-DD HH:MM:DD’ or ‘YYYYMMDDHHMMSS.uuuu’ format based on the configured time zone. However, while using the NOW() function, one must note that this function works only in a few databases such as MYSQL. For other popular databases like SQL Server and PostgreSQL, one can use equivalent functions such as getdate(), getutcdate() and CURRENT_TIMESTAMP() respectively.

In this article, we will be discussing the NOW() function in MYSQL in detail with the help of a few practical examples. But before beginning, let us discuss the syntax and arguments used in the function.

Syntax and parameters

The basic syntax used for the NOW() function is as follows :

NOW();

The function does not require any user-defined arguments.

Return type: The function returns a string/numeric value in the ‘YYYY-MM-DD HH:MM:DD’ (string) or ‘YYYYMMDDHHMMSS.uuuu’ (numeric) format.

The NOW()  function can be used as a part of the SELECT statement for fetching the current date and time based on the system settings, WHERE OR HAVING clause to filter based on the current time in the filtering expression, INSERT statement for data entering the current date and time for auditing or future references, etc.

Having discussed the basic syntax and parameters used for working with the NOW() function, let us go ahead and discuss a few examples to understand the functionality in great detail.

Examples of SQL now

Given below are the examples of SQL now:

Example #1

SQL query to illustrate the functionality of the NOW() function in MySQL.

SELECT NOW();

In this example, you can clearly see that the NOW() function returns the current system date and time. Whether we receive a numeric or string output depends upon the format specified in the system settings. The output is completely system dependent and hence may vary from one system to another.

Example #2

Find the date and time in the past 30 days based on the current timestamp.

SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);

In this example, we have tried to illustrate how to calculate the date and time of the past durations based on the current date and time settings. We have used a system-defined function DATE_SUB(arg, interval). The function basically subtracts a time value ( given as interval) from the argument date. In this case, the argument date is the output of the NOW() function, and the interval is 30 days. The function subtracts the 30 days from the current date and time.

You may play around with the DATE_SUB() function to get values in the other intervals such as a month, hour, minute, second, year, etc.

Example #3

Find the date and time in the past 1 hour based on the current timestamp.

SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);

This example is primarily an extension of the previous example. We have illustrated the use of DATE_SUB() to get the date and time of the past 1 hour, based on the current date and time, i.e. output of the NOW() function.

Example #4

Find the date and time after an interval of 1 year based on the current timestamp.

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

In the example, we have tried to illustrate how to calculate the date and time after a specified interval based on the current date and time. Here, we have used the DATE_ADD(arg, interval) function. Similar to the DATE_SUB() function, this is also a system-defined function in MYSQL that adds a time value( given interval) to the given date argument. In this case, the function adds the given interval of 1 year to the output of the NOW() function, i.e., the current date and time. We can use other intervals such as a month, hour, minute, second, day, etc., as we will see in the next example.

Example #5

Find the date and time after the next 10 days based on the current timestamp.

SELECT DATE_ADD(NOW(), INTERVAL 10 DAY);

In this example, we tried to illustrate the date and time calculation after 10 days based on the current date and time. This example is an extension of the previous example, as we have used the DATE_ADD() function with days as intervals.

Example #6

Find the order details for the orders that were placed in the past 1 weeks based on the given data.

CREATE TABLE orders(
order_id VARCHAR(10),
order_amount REAL,
order_time DATETIME
);
INSERT INTO orders (order_id,order_amount,order_time)
VALUES ('o1',345,'2020-09-22 10:23:32'),
('o2',143.54,'2020-09-28 10:13:20'),
('o3',564.32,'2020-09-23 12:23:32'),
('o4',345,'2020-09-22 11:11:21'),
('o5',125.78,'2020-09-20 11:23:32'),
('o6',34.98,'2020-09-19 11:13:25'),
('o7',78.92,'2020-09-19 10:23:32'),
('o8',132.67,'2020-09-20 12:28:26'),
('o9',786.54,'2020-09-28 10:37:29');

The data in the table looks something as follows :

SQL query to find orders which were placed in the past 1 week.

SELECT order_time, order_id, order_amount
FROM orders
WHERE order_time BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK)
AND NOW();

Conclusion – SQL now

NOW() is a system-defined function in the MYSQL database that returns a string or numeric value corresponding to the current date and time based on the system settings. The function is very important when you want to insert the timestamp of data entry in the database for auditing or tracking purposes, or when you want to filter records for cohort analysis, let’s say transactions in the past 30 days, past 12 hours, next week, etc.

Recommended Articles

This is a guide to SQL now. Here we discuss the basic syntax and parameters used for working with the NOW() function and discuss a few examples to understand the functionality in great detail. You may also have a look at the following articles to learn more –

1. SQL Alias
2. SQL UNION
3. SQL HOUR()
4. MySQL Repair Table