How the CURRENT_TIMESTAMP() function works in Mariadb?

The MariaDB CURRENT_TIMESTAMP() function is used to retrieve the current date and time in YYYY-MM-DD HH:MM:SS format, which is essential for tracking changes, logging events, and managing time-sensitive data within databases.

Syntax

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

CURRENT_TIMESTAMP()

This function does not accept any parameters and returns the current timestamp as a value in YYYY-MM-DD HH:MM:SS format.

Examples

Retrieve the Current Timestamp

To obtain the current timestamp, you would use the CURRENT_TIMESTAMP() function like this:

SELECT CURRENT_TIMESTAMP();

The output will be the current timestamp:

+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2024-03-17 09:25:11 |
+---------------------+

Timestamp Comparison

If you want to compare the current timestamp with a specific timestamp to determine if they are the same, you can do so using the CURRENT_TIMESTAMP() function:

SELECT CURRENT_TIMESTAMP() = '2024-03-17 08:56:29' AS Is_Current_Timestamp;

The output will indicate whether the current timestamp matches the specified one:

+----------------------+
| Is_Current_Timestamp |
+----------------------+
|                    0 |
+----------------------+

Extract Date from Current Timestamp

To extract the date part from the current timestamp, use the CURRENT_TIMESTAMP() function in conjunction with the DATE() function:

SELECT DATE(CURRENT_TIMESTAMP());

This will return the date part of the current timestamp:

+---------------------------+
| DATE(CURRENT_TIMESTAMP()) |
+---------------------------+
| 2024-03-17                |
+---------------------------+

Check if It’s Before Noon

You can check if the current time is before noon using the CURRENT_TIMESTAMP() function:

SELECT CURRENT_TIMESTAMP() < '2024-03-17 12:00:00' AS Is_Morning;

The output will indicate whether it’s before noon:

+------------+
| Is_Morning |
+------------+
|          1 |
+------------+

Use `CURRENT_TIMESTAMP()` in Conditional Statements

The CURRENT_TIMESTAMP() function can be used in conditional statements within your queries:

SELECT CASE
  WHEN CURRENT_TIMESTAMP() < '2024-03-17 12:00:00' THEN 'Good morning'
  WHEN CURRENT_TIMESTAMP() < '2024-03-17 18:00:00' THEN 'Good afternoon'
  ELSE 'Good evening'
END AS Greeting;

The output will provide an appropriate greeting based on the current timestamp:

+--------------+
| Greeting     |
+--------------+
| Good morning |
+--------------+

Here are a few functions related to the MariaDB CURRENT_TIMESTAMP() function:

MariaDB NOW() function is used to retrieve the current date and time, similar to CURRENT_TIMESTAMP().
MariaDB SYSDATE() function returns the current date and time, but unlike NOW(), it returns the exact time the function is executed.
MariaDB UNIX_TIMESTAMP() function returns the current date and time as a Unix timestamp.

Conclusion

The CURRENT_TIMESTAMP() function in MariaDB is a vital tool for working with temporal data. It provides an easy way to capture the exact moment of data entry or modification, which is crucial for maintaining accurate records and performing time-based analysis. By leveraging this function, developers and database administrators can ensure that their applications and databases handle time-related data efficiently and accurately.