How the TIMESTAMP() function works in Mariadb?

The TIMESTAMP() function in MariaDB is used to convert a date or datetime expression into a DATETIME value. It can also be used to add a time value to a date or datetime expression, resulting in a new DATETIME value.

Syntax

The syntax of the MariaDB TIMESTAMP() function has two variations:

TIMESTAMP(expr)
TIMESTAMP(expr1, expr2)

1. With a single argument:

   • expr: This is the date or datetime expression to be converted into a DATETIME value.

2. With two arguments:

   • expr1: This is the date or datetime expression.
   • expr2: This is the time expression to be added to expr1.

The function returns a DATETIME value.

Examples

Example 1: Converting a date expression to a datetime value

This example demonstrates how to convert a date expression into a datetime value using the TIMESTAMP() function with a single argument.

SELECT TIMESTAMP('2023-05-01');

The following is the output:

+-------------------------+
| TIMESTAMP('2023-05-01') |
+-------------------------+
| 2023-05-01 00:00:00     |
+-------------------------+

In this example, the TIMESTAMP() function converts the date expression ‘2023-05-01’ into a DATETIME value by appending ‘00:00:00’ as the time portion.

Example 2: Converting a datetime expression to a datetime value

This example shows how to convert a datetime expression into a datetime value using the TIMESTAMP() function with a single argument.

SELECT TIMESTAMP('2023-05-01 10:30:00');

The following is the output:

+----------------------------------+
| TIMESTAMP('2023-05-01 10:30:00') |
+----------------------------------+
| 2023-05-01 10:30:00              |
+----------------------------------+

In this example, the TIMESTAMP() function simply returns the input datetime expression ‘2023-05-01 10:30:00’ as a DATETIME value.

Example 3: Adding a time expression to a date expression

This example demonstrates how to add a time expression to a date expression using the TIMESTAMP() function with two arguments.

SELECT TIMESTAMP('2023-05-01', '10:30:00');

The following is the output:

+-------------------------------------+
| TIMESTAMP('2023-05-01', '10:30:00') |
+-------------------------------------+
| 2023-05-01 10:30:00                 |
+-------------------------------------+

In this example, the TIMESTAMP() function adds the time expression ‘10:30:00’ to the date expression ‘2023-05-01’, resulting in the DATETIME value ‘2023-05-01 10:30:00’.

Example 4: Using TIMESTAMP() with a table

This example shows how to use the TIMESTAMP() function in combination with a table.

DROP TABLE IF EXISTS events;
CREATE TABLE events (
    event_name VARCHAR(255),
    event_date DATE,
    event_time TIME
);
INSERT INTO events VALUES
    ('Meeting', '2023-05-01', '09:00:00'),
    ('Presentation', '2023-05-02', '13:30:00');

SELECT event_name, TIMESTAMP(event_date, event_time) AS event_datetime
FROM events;

The following is the output:

+--------------+---------------------+
| event_name   | event_datetime      |
+--------------+---------------------+
| Meeting      | 2023-05-01 09:00:00 |
| Presentation | 2023-05-02 13:30:00 |
+--------------+---------------------+

In this example, the TIMESTAMP() function is used to combine the event_date and event_time columns from the events table into a single DATETIME value, which is included in the result set along with the event_name.

Example 5: Using TIMESTAMP() with the NOW() function

This example demonstrates how to use the TIMESTAMP() function in combination with the NOW() function to get the current timestamp.

SELECT TIMESTAMP(NOW());

The following is the output:

+---------------------+
| TIMESTAMP(NOW())    |
+---------------------+
| 2023-05-03 15:45:30 |
+---------------------+

In this example, the TIMESTAMP() function converts the result of the NOW() function (which returns the current date and time) into a DATETIME value, effectively returning the current timestamp.

Related Functions

The following are some functions related to the MariaDB TIMESTAMP() function:

• MariaDB NOW() function is used to retrieve the current date and time.
• MariaDB CURDATE() function is used to retrieve the current date.
• MariaDB CURTIME() function is used to retrieve the current time.
• MariaDB FROM_UNIXTIME() function is used to convert a Unix timestamp value into a DATETIME value.

Conclusion

The TIMESTAMP() function in MariaDB is a versatile tool for working with date and time values. It can be used to convert various date and time expressions into DATETIME values, as well as to combine date and time expressions into a single DATETIME value. By understanding the usage and capabilities of this function, along with related functions, developers can effectively manipulate and work with date and time data in their MariaDB applications.