In MySQL, the TIMESTAMP() function returns a datetime value based on the argument/s passed in. You can provide one argument or two. If you provide two, it adds the second one to the first and returns the result.

Syntax

You can use either of these two forms:

TIMESTAMP(expr)
TIMESTAMP(expr1,expr2)

The first argument (expr and expr1) is a date or datetime expression. The second argument (expr2) is a time expression. If you provide two arguments, expr2 is added to expr1 and the result is a datetime value.

Example 1 – Provide a ‘date’ Argument

In this example I provide a date expression.

SELECT TIMESTAMP('1999-12-31');

Result:

+-------------------------+
| TIMESTAMP('1999-12-31') |
+-------------------------+
| 1999-12-31 00:00:00     |
+-------------------------+

So the result is a datetime value regardless of whether our argument was a date or datetime expression.

Example 2 – Provide a ‘datetime’ Argument

And here’s an example using a datetime expression.

SELECT TIMESTAMP('1999-12-31 23:59:59');

Result:

+----------------------------------+
| TIMESTAMP('1999-12-31 23:59:59') |
+----------------------------------+
| 1999-12-31 23:59:59              |
+----------------------------------+

Example 3 – Include Fractional Seconds

You can also include a fractional seconds part up to microseconds (6 digits). When you do this, the result will also include that part.

SELECT TIMESTAMP('1999-12-31 23:59:59.999999');

Result:

+-----------------------------------------+
| TIMESTAMP('1999-12-31 23:59:59.999999') |
+-----------------------------------------+
| 1999-12-31 23:59:59.999999              |
+-----------------------------------------+

Example 4 – Providing 2 Arguments

Here’s an example using two arguments. As mentioned, the second one gets added to the first.

SELECT TIMESTAMP('1999-12-31', '12:30:45');

Result:

+-------------------------------------+
| TIMESTAMP('1999-12-31', '12:30:45') |
+-------------------------------------+
| 1999-12-31 12:30:45                 |
+-------------------------------------+

Example 5 – Larger ‘time’ Values

The time data type can have a range from -838:59:59 to 838:59:59. This is because it’s not just limited to representing the time of day. It could also be used to represent elapsed time. In this case, we use it to add a large number of hours to a date value.

SELECT TIMESTAMP('1999-12-31', '400:30:45');

Result:

+--------------------------------------+
| TIMESTAMP('1999-12-31', '400:30:45') |
+--------------------------------------+
| 2000-01-16 16:30:45                  |
+--------------------------------------+

Example 6 – Negative Values

You can subtract the second argument from the first, simply by prefixing the second value with a minus sign.

SELECT TIMESTAMP('1999-12-31', '-400:30:45');

Result:

+---------------------------------------+
| TIMESTAMP('1999-12-31', '-400:30:45') |
+---------------------------------------+
| 1999-12-14 07:29:15                   |
+---------------------------------------+

Example 7 – Using the Current Date

If you want a timestamp that uses the current date, you might be more interested in functions such as NOW(), CURDATE(), and possibly even SYSDATE() (for the difference, see SYSDATE() vs NOW()).

However, the TIMESTAMP() function may still be your preferred function in certain cases, for example if you want to add a time value to the current date.

SELECT 
    TIMESTAMP(CURDATE()) AS 'Today',
    TIMESTAMP(CURDATE(), '24:00:00') AS 'Tomorrow';

Result:

+---------------------+---------------------+
| Today               | Tomorrow            |
+---------------------+---------------------+
| 2018-07-03 00:00:00 | 2018-07-04 00:00:00 |
+---------------------+---------------------+

The TIMESTAMPADD() Function

You can use the TIMESTAMPADD() function to add a specified unit to a date or datetime value. This function also accepts units such as days, months, years etc.

If you find TIMESTAMP() too restrictive for your needs, see TIMESTAMPADD() Examples.