EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL now
 

SQL now

Updated March 10, 2023

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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();

SQL now output 1

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);

SQL now output 2

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);

SQL now output 3

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);

output 4

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);

output 5

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 :

output 6

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();

output 7

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

We hope that this EDUCBA information on “SQL now” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW