How the SYSDATE() function works in Mariadb?

The SYSDATE() function in MariaDB is used to retrieve the current date and time from the server’s system clock. It is a useful function for various purposes, such as logging events, tracking record creation or modification times, and performing date-based calculations.

Syntax

The syntax for the MariaDB SYSDATE() function is as follows:

SYSDATE([fsp])

• fsp (optional): An integer value representing the fractional seconds precision. If omitted, the default precision is 0, which returns the current date and time without fractional seconds.

The SYSDATE() function returns a DATETIME or STRING value, depending on the context in which it is used.

Examples

Example 1: Retrieving the current date and time

This example demonstrates how to use the SYSDATE() function to retrieve the current date and time.

SELECT SYSDATE();

The following is the output:

+---------------------+
| SYSDATE()           |
+---------------------+
| 2024-03-09 15:50:57 |
+---------------------+

This query returns the current date and time in the format ‘YYYY-MM-DD HH:MM:SS’.

Example 2: Retrieving the current date and time with fractional seconds

This example shows how to retrieve the current date and time with fractional seconds by specifying the fsp (fractional seconds precision) parameter.

SELECT SYSDATE(6);

The following is the output:

+----------------------------+
| SYSDATE(6)                 |
+----------------------------+
| 2024-03-09 15:51:21.691056 |
+----------------------------+

In this example, the fractional seconds precision is set to 6, which means the returned value includes up to 6 digits of fractional seconds.

Example 3: Using SYSDATE() in a table

This example demonstrates how to use the SYSDATE() function to insert the current date and time into a table.

DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event VARCHAR(100),
    created_at DATETIME DEFAULT SYSDATE()
);

INSERT INTO logs (event) VALUES ('User logged in');
SELECT * FROM logs;

The following is the output:

+----+----------------+---------------------+
| id | event          | created_at          |
+----+----------------+---------------------+
|  1 | User logged in | 2024-03-09 15:51:32 |
+----+----------------+---------------------+

In this example, the SYSDATE() function is used as the default value for the created_at column, which stores the current date and time when a new row is inserted into the logs table.

Example 4: Using SYSDATE() in a WHERE clause

This example demonstrates how to use the SYSDATE() function in a WHERE clause to filter records based on a date range.

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_date, total_amount) VALUES
    ('2024-03-01 10:00:00', 100.50),
    ('2024-03-05 15:30:00', 75.25),
    ('2024-03-09 08:45:00', 120.75);

SELECT *
FROM orders
WHERE order_date BETWEEN DATE_SUB(SYSDATE(), INTERVAL 7 DAY) AND SYSDATE();

The following is the output:

+----+---------------------+--------------+
| id | order_date          | total_amount |
+----+---------------------+--------------+
|  2 | 2024-03-05 15:30:00 |        75.25 |
|  3 | 2024-03-09 08:45:00 |       120.75 |
+----+---------------------+--------------+

In this example, the query retrieves orders placed within the last 7 days from the current date and time. The SYSDATE() function is used in the WHERE clause to filter the records based on the order_date column.

Example 5: Using SYSDATE() with NOW()

This example compares the SYSDATE() and NOW() functions, which both return the current date and time but have slightly different behaviors.

SELECT SYSDATE(), NOW();

The following is the output:

+---------------------+---------------------+
| SYSDATE()           | NOW()               |
+---------------------+---------------------+
| 2024-03-09 15:52:30 | 2024-03-09 15:52:30 |
+---------------------+---------------------+

In this example, both SYSDATE() and NOW() return the same value, which is the current date and time. However, SYSDATE() is a non-deterministic function, meaning it retrieves the current time from the server’s system clock each time it is called, while NOW() is a semi-deterministic function, which retrieves the current time from the server’s system clock once per statement execution.

Related Functions

The following are a few functions related to the MariaDB SYSDATE() function:

• The CURRENT_TIMESTAMP() function returns the current date and time as a TIMESTAMP value.
• The NOW() function returns the current date and time as a DATETIME or STRING value, similar to SYSDATE().
• The UNIX_TIMESTAMP() function returns the current date and time as a Unix timestamp (the number of seconds since the Unix epoch).
• The DATE() function extracts the date part from a DATETIME or TIMESTAMP value.
• The TIME() function extracts the time part from a DATETIME or TIMESTAMP value.

Conclusion

The SYSDATE() function in MariaDB is a versatile tool for retrieving the current date and time from the server’s system clock. It can be used in various contexts, such as logging events, tracking record creation or modification times, and performing date-based calculations. By understanding the syntax and usage of this function, along with related functions like NOW() and CURRENT_TIMESTAMP(), developers can effectively handle date and time operations in their MariaDB applications.