How TIMESTAMP() Works in MariaDB

In MariaDB, TIMESTAMP() is a built-in date and time function that returns a datetime value, based on its argument/s.

It can be used with either one argument or two, as follows:

When used with one argument, it returns that date or datetime expression as a datetime value.
When used with two arguments, it adds the second (time) argument to the first (date or datetime) expression, then returns the resulting datetime value.

Syntax

It can be used in the following two ways:

TIMESTAMP(expr)
TIMESTAMP(expr1,expr2)

Where expr1 is a date or datetime expression, and expr2 is a time expression to add to expr1.

Example

Here’s an example to demonstrate:

SELECT TIMESTAMP('2030-02-01');

Result:

+-------------------------+
| TIMESTAMP('2030-02-01') |
+-------------------------+
| 2030-02-01 00:00:00     |
+-------------------------+

Datetime Values

Here’s an example of passing a datetime value:

SELECT TIMESTAMP('2030-02-01 10:30:45');

Result:

+----------------------------------+
| TIMESTAMP('2030-02-01 10:30:45') |
+----------------------------------+
| 2030-02-01 10:30:45              |
+----------------------------------+

Second Argument

Here’s an example that adds the second argument to the first:

SELECT TIMESTAMP('2030-02-01 10:30:45', '02:15:15');

Result:

+----------------------------------------------+
| TIMESTAMP('2030-02-01 10:30:45', '02:15:15') |
+----------------------------------------------+
| 2030-02-01 12:46:00                          |
+----------------------------------------------+

Microseconds

Here’s an example that adds microseconds:

SELECT TIMESTAMP('2030-02-01 10:30:45', '00:00:00.123456');

Result:

+-----------------------------------------------------+
| TIMESTAMP('2030-02-01 10:30:45', '00:00:00.123456') |
+-----------------------------------------------------+
| 2030-02-01 10:30:45.123456                          |
+-----------------------------------------------------+

Negative Times

Negative times are valid:

Example:

SELECT TIMESTAMP('2030-02-01 10:30:45', '-09:20:00');

Result:

+-----------------------------------------------+
| TIMESTAMP('2030-02-01 10:30:45', '-09:20:00') |
+-----------------------------------------------+
| 2030-02-01 01:10:45                           |
+-----------------------------------------------+

Current Date

We can pass NOW() as the datetime argument to use the current date and time:

SELECT 
    NOW(),
    TIMESTAMP(NOW(), '10:30:45');

Result:

+---------------------+------------------------------+
| NOW()               | TIMESTAMP(NOW(), '10:30:45') |
+---------------------+------------------------------+
| 2021-05-28 09:25:09 | 2021-05-28 19:55:54          |
+---------------------+------------------------------+

Invalid Arguments

When passed an invalid argument, TIMESTAMP() returns null with a warning:

SELECT TIMESTAMP('Ten Thirty AM');

Result:

+----------------------------+
| TIMESTAMP('Ten Thirty AM') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set, 1 warning (0.004 sec)

Check the warning:

SHOW WARNINGS;

Result:

+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Incorrect time value: 'Ten Thirty AM' |
+---------+------+---------------------------------------+

Missing Argument

Calling TIMESTAMP() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT TIMESTAMP();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

And another example:

SELECT TIMESTAMP('2020-12-09', '06:30:45', '06:30:45');

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' '06:30:45')' at line 1