MySQL | TIMESTAMP method
Start your free 7-days trial now!
MySQL's TIMESTAMP(~) method returns the input as a datetime value when used with a single argument, and returns the sum of the arguments when used with two arguments.
Syntax
SELECT TIMESTAMP(expr);SELECT TIMESTAMP(expr1, expr2);
Parameters
1. expr | date/datetime
The date/datetime to return the datetime for.
2. expr1 | date/datetime
A date/datetime to add the time provided in expr2 to.
3. expr2 | time
A time value to add to the date/datetime provided in expr1.
We either provide one argument expr or two arguments expr1 and expr2 as inputs to this method.
Return value
Case | Return value |
|---|---|
Single argument provided | Datetime value |
Two arguments provided | Sum of two arguments |
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Basic usage
To return student enrollment dates as a datetime:
SELECT fname, day_enrolled, TIMESTAMP(day_enrolled)FROM students;
+----------+--------------+-------------------------+| fname | day_enrolled | TIMESTAMP(day_enrolled) |+----------+--------------+-------------------------+| Sky | 2015-12-03 | 2015-12-03 00:00:00 || Ben | 2016-04-20 | 2016-04-20 00:00:00 || Travis | 2018-08-14 | 2018-08-14 00:00:00 || Arthur | 2016-04-01 | 2016-04-01 00:00:00 || Benjamin | 2014-01-01 | 2014-01-01 00:00:00 |+----------+--------------+-------------------------+
Note that dates are assumed to have time value of 00:00:00 when converting to datetime.
To return the sum of two arguments:
SELECT TIMESTAMP('2019-10-23 07:00:00', '13:00:00');
+----------------------------------------------+| TIMESTAMP('2019-10-23 07:00:00', '13:00:00') |+----------------------------------------------+| 2019-10-23 20:00:00 |+----------------------------------------------+
To perform summation with negative time:
SELECT TIMESTAMP('2019-10-23 07:00:00', '-13:00:00');
+-----------------------------------------------+| TIMESTAMP('2019-10-23 07:00:00', '-13:00:00') |+-----------------------------------------------+| 2019-10-22 18:00:00 |+-----------------------------------------------+
In this case we are subtracting 13 hours from the input datetime '2019-10-23 07:00:00'.
