How the NOW() function works in Mariadb?

The NOW() function is a date and time function that returns the current date and time. It is equivalent to using the CURRENT_TIMESTAMP function or the CURRENT_TIMESTAMP keyword. The NOW() function returns a value of the datetime data type, which has the format YYYY-MM-DD HH:MM:SS.

Syntax

The syntax of the NOW() function is as follows:

NOW([precision])

• precision is an optional parameter that specifies the number of fractional digits for the seconds part of the result. It can be an integer from 0 to 6. The default value is 0, which means no fractional digits.

Examples

Example 1: Using NOW() without precision

The following example uses the NOW() function without specifying the precision to get the current date and time.

SELECT NOW() AS current_datetime;

The output is:

+---------------------+
| current_datetime    |
+---------------------+
| 2024-02-14 21:31:31 |
+---------------------+

Note that the result has no fractional digits for the seconds part.

Example 2: Using NOW() with precision

The following example uses the NOW() function with a precision of 3 to get the current date and time with milliseconds.

SELECT NOW(3) AS current_datetime;

The output is:

+-------------------------+
| current_datetime        |
+-------------------------+
| 2024-02-14 21:31:31.123 |
+-------------------------+

Note that the result has three fractional digits for the seconds part.

Example 3: Using NOW() in a table

The following example creates a table named logs with a column named created_at that uses the NOW() function as the default value. This means that whenever a new row is inserted into the table, the created_at column will store the current date and time.

CREATE TABLE logs (
  id INT PRIMARY KEY,
  message VARCHAR(255),
  created_at DATETIME DEFAULT NOW()
);

The output is:

Query OK, 0 rows affected

The following example inserts two rows into the logs table with different messages.

INSERT INTO logs (id, message) VALUES (1, 'Hello'), (2, 'World');

The output is:

Query OK, 2 rows affected

The following example selects all the rows from the logs table.

SELECT * FROM logs;

The output is:

+----+---------+---------------------+
| id | message | created_at          |
+----+---------+---------------------+
|  1 | Hello   | 2024-02-14 21:31:31 |
|  2 | World   | 2024-02-14 21:31:32 |
+----+---------+---------------------+

Note that the created_at column has the date and time when the rows were inserted.

Example 4: Using NOW() in a query

The following example uses the NOW() function in a query to filter the rows from the orders table where the order_date is within the last 30 days.

SELECT id, customer_id, order_date, total_amount
FROM orders
WHERE order_date >= NOW() - INTERVAL 30 DAY;

The output is:

+----+-------------+------------+---------------+
| id | customer_id | order_date | total_amount  |
+----+-------------+------------+---------------+
|  3 |           2 | 2024-01-20 |        300.00 |
|  4 |           2 | 2024-02-10 |        500.00 |
|  5 |           3 | 2024-02-15 |        300.00 |
|  6 |           4 | 2024-02-20 |        400.00 |
+----+-------------+------------+---------------+

Note that the NOW() function returns the current date and time, and the INTERVAL keyword is used to subtract a specified amount of time from it.

Related Functions

Some of the functions that are related to the NOW() function are:

• CURRENT_TIMESTAMP: This is a synonym for the NOW() function. It returns the current date and time as a value of the datetime data type.
• CURRENT_DATE: This is a date and time function that returns the current date as a value of the date data type. It is equivalent to using the DATE(NOW()) function or the CURDATE() function.
• CURRENT_TIME: This is a date and time function that returns the current time as a value of the time data type. It is equivalent to using the TIME(NOW()) function or the CURTIME() function.
• SYSDATE: This is a date and time function that returns the current date and time as a value of the datetime data type. It is similar to the NOW() function, but it does not use the session time zone, and it is not affected by the SET TIMESTAMP statement.

For example, the following query uses the CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, and SYSDATE functions to get the current date and time in different formats.

SELECT CURRENT_TIMESTAMP AS current_timestamp,
       CURRENT_DATE AS current_date,
       CURRENT_TIME AS current_time,
       SYSDATE() AS sysdate;

The output is:

+---------------------+--------------+--------------+---------------------+
| current_timestamp   | current_date | current_time | sysdate             |
+---------------------+--------------+--------------+---------------------+
| 2024-02-14 21:31:31 | 2024-02-14   | 21:31:31     | 2024-02-14 21:31:31 |
+---------------------+--------------+--------------+---------------------+

Conclusion

The NOW() function is a useful way to get the current date and time in Mariadb. It returns a value of the datetime data type, which has the format YYYY-MM-DD HH:MM:SS. It can take an optional precision parameter that specifies the number of fractional digits for the seconds part. It can be used in tables, queries, and other expressions that require the current date and time. Some of the related functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, and SYSDATE.