SQL NOW() Function

The SQL NOW() is a function, and returns a date and time specifying the current date and time according your computer's system date and time.

It can be used in SELECT statement as well in where clause.

Using any inbuilt Date-and-Time function, use any expression in the WHERE clause.

SELECT this, that
FROM here
WHERE start >= NOW();

When the NOW() method is used in a numeric environment, it produces a number; you can use it to calculate now minus 1 hour.

In MySQL, subtract 1 hour from DateTime using one of the methods below. The initial strategy is as follows:

Case 1 - Using DATE_ADD()

select date_add(yourColumnName,interval -1 hour) from yourTableName;

Case 2 - Using DATE_SUB()

select date_sub(yourColumnName,interval 1 hour) from yourTableName;

Example 1: The query to subtract 1 hour from DateTime is as follows. With date_add, we have set a negative date:

select date_add(now(),interval -1 hour);

Output:

Example 2: Implement the second method using date_sub(). The query is as follows:

select date_sub(now(),interval 1 hour);

The following output displays the date subtracting 1 hour :

Example 3: The following statement returns the current date and time, now minus 1 hour :

SELECT (NOW() - INTERVAL 1 HOUR) 'NOW - 1 hour',
        NOW();

To decrease one day from the current datetime, we must first obtain information about the current datetime, and then use MySQL's now() method. The now() function returns the current date and time.

INTERVAL also works as expected, for example, INTERVAL 1 DAY = 24 hours.

DATE_SUB() from MySQL is the function to utilise for this.

Syntax:

Here is the syntax to subtract 1 day from current datetime:-

DATE_SUB(NOW(),INTERVAL  1 DAY);

The preceding syntax evaluates the current datetime first, then subtracts one day in the second step.

Example 1: Use CURDATE() - INTERVAL 1 DAY to acquire a complete day. Regardless of when the script is run, it will return to the previous day's commencement.

SELECT * FROM FOO
WHERE MY_DATE_FIELD >= NOW() - INTERVAL 1 DAY;

Example 2: Now, let us used the date_sub() method to subtract 1 day. The query is as follows:

select date_sub(now(),interval 1 day);

Output:

Example 3: Instead of now(), you can use curdate() to receive simply the date value as a result. This is a demonstration of curdate ().

select date_sub(curdate(),interval 1 day);

Output:

Let's imagine you have a PostgreSQL database called sales(order id, order date, amount, product id). Here is a query to subtract one minute from the order date field of the sales table.

Not only columns, but also system datetime values can be used with the interval operator. In PostgreSQL, below are some queries that add/subtract minutes, hours, days, and months to the current datetime. In PostgreSQL, the now() function returns the current datetime.

# add/subtract 1 minute from present date/time value

psql> select now() + interval '1 minute' from sales;
psql> select now() - interval '1 minute' from sales;

We ought to add a day to the current datetime, as stated in the now()+1 day statement.

Example 1: You can write the above logic like this:

now()+interval 1 day;

Or you can write same logic with date_add() function from MySQL like this:

date_add(now(),interval 1 day) 

Output:

Example 2: Here increments a day by 1 :

mysql> select now()+ interval 1 day;

Output:

Example 3: displays only the incremented date with curdate():

mysql> select curdate()+interval 1 day;

Output:

Example 4: Displays only the incremented date with date_add():

mysql> select date_add(curdate(),interval 1 day);

Output:

The MySQL NOW() method returns the current date and time in the specified time zone as a string or a number in the format 'YYYY-MM-DD HH:MM:DD' or 'YYYYMMDDHHMMSS.uuuuuu'.

The NOW() function's returned type is determined on the context in which it is invoked.

Example 1: The NOW() method, for example, returns the current date and time as a string in the following sentence:

SELECT NOW();

Example 2: The NOW() function, on the other hand, returns the current date and time as a number in the numeric context, as shown in the given description:

SELECT NOW() + 0;

Example 3: It's worth noting that the NOW() function delivers a fixed date and time when the statement began to run. Consider the following scenario:

SELECT NOW(), SLEEP(5), NOW();

The first NOW() function in the query was called, and the SLEEP(5) function delayed the query's execution for 5 seconds before the second NOW() function was executed. Despite the fact that they were called at different times, both NOW() methods return the same value.

Example 4: Instead, use SYSDATE() to get the precise time at which the statement executes; see the following example:

SELECT SYSDATE(), SLEEP(5), SYSDATE();

Example 5: If you want to update the current date and time returned by the NOW() function, you can use the following statement to change the MySQL server's time zone:

SET time_zone = your_time_zone;

Main Article :- Sql difference between NOW() and SYSDATE() Functions

There are a variety of SQL NOW() and SYSDATE() methods that return current timestamp values. There are two functions, however, that look to achieve the same thing but are actually quite different.

Note: The value produced by NOW(), but not by SYSDATE(), is affected by the SET TIMESTAMP command.

In many circumstances, regardless of the function you employ, you'll likely obtain the same result. However, if your statement is more sophisticated and takes a long time to execute, the return result may fluctuate significantly depending on which function you employ.

Example 1: Here's an example of how the return value varies based on the function you're using.

SELECT SYSDATE(), 
SLEEP(10) AS '', SYSDATE(),
NOW(), SLEEP(10) AS '', NOW();

Result:

So, in this example, I call SYSDATE() twice with a 10-second break between them. Then I repeat the process with NOW ().

The 10 second pause has an effect on the return value of the second SYSDATE() but not the second NOW(), as intended. In fact, the values for both occurrences of NOW() are the same as the first instance of SYSDATE(), which is when the statement began to run.

Example 2: Following example will show the difference between these functions:

Select NOW(), SLEEP(5), NOW();

The preceding query demonstrates that the NOW() function returns the time at which it began to execute because it returns the same value after 5 seconds of system sleep.

Select SYSDATE(), SLEEP(5), SYSDATE();

In comparison, the above query demonstrates that the SYSDATE() function returns the time at which it executes because it returns a value that is actually incremented by 5 seconds after 5 seconds of system sleep.

To set a default value for a DATETIME or TIMESTAMP column, use the NOW() method. MySQL inserts the current date and time into the column whose default value is NOW() when you don't specify a date or time value in the INSERT statement.

Example 1: Let’s take a look at the following example.

To begin, make a new table called tmp, with three columns: id, title, and created_on. The NOW() function specifies a default value for the created_on column.

CREATE TABLE tmp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    created_on DATETIME NOT NULL DEFAULT NOW() -- or CURRENT_TIMESTAMP
);

Notice that CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW() so you can use them interchangeably.

Second, insert a new row into the tmp table without specifying the value for the created_on column:

INSERT INTO tmp(title)
VALUES('Test NOW() function');

Third, query the data from the tmp table:

SELECT * FROM tmp; 

MySQL NOW as default value

The created_on column's value has been changed to the current date and time when the INSERT statement was run.

Example 2: You can acquire the current date and time with timezone using the CURRENT_ TIME or CURRENT_TIMESTAMP parameters of the NOW() function:

SELECT CURRENT_TIME, CURRENT_TIMESTAMP;

Output:

Example 3: The LOCALTIME and LOCALTIMESTAMP functions are used to get the current date and time without a time zone.

SELECT LOCALTIME, LOCALTIMESTAMP;

Output:

In MySQL, the built-in method Now() returns the current date and time.

Syntax of NOW():

To get the current date and time in MySQL, use the NOW() function.

NOW()

Example 1: Use the DATE() function in conjunction with the NOW() method, for example, to extract only the current date from the NOW() function:

SELECT DATE(NOW());

Output:

The current date has been taken from the NOW() function using the DATE() function, as seen in the output.

Example 2:

SELECT NOW(); 

Output:

Now as a number ( SELECT NOW() + 0 )

Output:

Note: This is in the format YYY-MM-DD HH:MM:SS

Example 3: Here example for One hour back, Present, One hour After :

SELECT NOW()- interval 1 HOUR  as One_hour_back,
 NOW() as Present, 
 NOW()+ interval 1 HOUR as One_hour_After

Output:

Example 1: You would like to display yesterday's date (without time) in an SQLite database.

SELECT DATE('now','-1 day') AS yesterday_date;

Assuming today is 2020-09-24, the result is:

Discussion: Subtract one day from today's date to get yesterday's date. To get the current date, use now(). The DATE() method in SQLite allows you to subtract or add any number of days, months, or years. You use DATE('now','-1 day') to retrieve yesterday's date since you need to subtract one day.

Example 2: You may easily go back in time to any point in time. Here's how you'd go back five months and three days, as an example.

SELECT DATE('now', '-5 months', '-3 days') AS modified_date;

Example 3: You can also calculate tomorrow's date. To do so, you need to add one day.

SELECT DATE('now', '+1 day') AS tomorrow_date;

Example 4: Suppose we want to get tomorrow's date, the stored function makes it very easy. See the following query:

SELECT today() + interval 1 day AS Tomorrow;

After execution, it will return the tomorrow date.

Example 5: Suppose we want to get yesterday's date, the stored function can be used like the following query:

SELECT today() - interval 1 day AS Yesterday;  

After execution, we will get the yesterday's date.

Example: Here’s the SQL query to get records from last 7 days in MySQL.

mysql> select * from sales
     where order_date > now() - INTERVAL 7 day;

Output:

We choose entries in the above query where order date falls after a 7-day gap in the past. To acquire the most recent datetime value, we utilise the system function now(), and the INTERVAL clause to determine a date 7 days in the past.

You can also use current_date instead of now():

mysql> select * from sales
     where order_date > current_date - interval 7 day;