The TIMESTAMP SQL Function in MySQL and MariaDB - Date from timestamp with addition |
|
| | TIMESTAMP | Syntax: | TIMESTAMP(Date [, Addition]) | | Return value: | DATETIME | | Function type: | Date and Time function | |
| | The TIMESTAMP() function returns the value of "Date" as a DATETIME expression, or "Addition" if specified, added to "Date" .
"Date" must be of type DATE or DATETIME or an integer value of a timestamp in the format YYYYMMDDhhmmss.
The "Addition" argument must be of type TIME. Format as string: hh:mm:ss(.microseconds) or as integer: hhmmss(.microseconds).
If either the "Date" or "Addition" argument is invalid or NULL, the TIMESTAMP() function returns NULL. | SQL Examples for the TIMESTAMP function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
| SELECT now(), timestamp(now());
SELECT utc_timestamp()+1, timestamp(utc_timestamp()+1);
SELECT current_timestamp()+1, timestamp(current_timestamp()+1);
select timestamp('2023-03-22 12:15:30');
select timestamp('2023-03-22 12:15:30', '00:15:05');
select timestamp('2023-03-22 12:15:30', 1505);
select timestamp(20230322121530);
select timestamp(20230322121530, 1505);
select timestamp(20230322121530, 2031505);
select timestamp(20230322121530, 1505.099);
select timestamp(20230322121530, '00:15:05.099');
select timestamp(20230322121530, '300:15:05.099');
select timestamp('2023-03-22 12:15:30', '0000-00-01 00:15:05');
SELECT unix_timestamp(), timestamp(unix_timestamp());
select timestamp(null);
select timestamp(now(), null);
|
|
| now() |  |
| timestamp(now()) |
|---|
| datetime(19) | datetime(19) |
|---|
| 2023-03-30 22:29:43 | 2023-03-30 22:29:43 |
|
|
|
|
| utc_timestamp()+1 | |
| timestamp(utc_timestamp()+1) |
|---|
| bigint(16) | datetime(19) |
|---|
| 20230330202944 | 2023-03-30 20:29:44 |
|
|
|
|
| current_timestamp()+1 | |
| timestamp(current_timestamp()+1) |
|---|
| bigint(16) | datetime(19) |
|---|
| 20230330222944 | 2023-03-30 22:29:44 |
|
|
|
|
| timestamp('2023-03-22 12:15:30') |
|---|
| datetime(19) |
|---|
| 2023-03-22 12:15:30 |
|
|
| timestamp('2023-03-22 12:15:30', '00:15:05') |
|---|
| datetime(19) |
|---|
| 2023-03-22 12:30:35 |
|
|
| timestamp('2023-03-22 12:15:30', 1505) |
|---|
| datetime(19) |
|---|
| 2023-03-22 12:30:35 |
|
|
| timestamp(20230322121530) |
|---|
| datetime(19) |
|---|
| 2023-03-22 12:15:30 |
|
|
| timestamp(20230322121530, 1505) |
|---|
| datetime(19) |
|---|
| 2023-03-22 12:30:35 |
|
|
| timestamp(20230322121530, 2031505) |
|---|
| datetime(19) |
|---|
| 2023-03-30 23:30:35 |
|
|
| timestamp(20230322121530, 1505.099) |
|---|
| datetime(23) |
|---|
| 2023-03-22 12:30:35.099 |
|
|
| timestamp(20230322121530, '00:15:05.099') |
|---|
| datetime(23) |
|---|
| 2023-03-22 12:30:35.099 |
|
|
| timestamp(20230322121530, '300:15:05.099') |
|---|
| datetime(23) |
|---|
| 2023-04-04 00:30:35.099 |
|
|
| timestamp('2023-03-22 12:15:30', '0000-00-01 00:15:05') |
|---|
| datetime(19) |
|---|
| NULL |
|
|
| unix_timestamp() | |
| timestamp(unix_timestamp()) |
|---|
| bigint(17) | datetime(19) |
|---|
| 1680208183 | NULL |
|
|
|
|
| timestamp(null) |
|---|
| datetime(19) |
|---|
| NULL |
|
|
| timestamp(now(), null) |
|---|
| datetime(19) |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the TIMESTAMP() function in MySQL and MariaDB databases | In MySQL and MariaDB, the TIMESTAMP() function can be used to convert a specific expression into a "date and time" value. The function accepts one or more arguments, which can be columns, constants, or functions that return a valid date and time. TIMESTAMP() can take various forms of input expressions, such as strings, numeric values, or date functions, and is commonly used to convert separate date and time components or multiple expressions into a single date and time value to work in date-time queries.
The function performs limited error checking. If an invalid value is specified as an argument, it can produce no results or unexpected results. Expressions should therefore be in a format that can be interpreted as a date and time. | | Further MySQL und MariaDB SQL Date and Time functions | | ADDDATE | ADDDATE(Date, [INTERVAL] Number [Unit]) | | More about ADDDATE Function |
| | ADDTIME | ADDTIME(Date, Expression) | | More about ADDTIME Function |
| | CONVERT_TZ | CONVERT_TZ(Date, From time zone, To time zone) | | More about CONVERT_TZ Function |
| | CURDATE | CURDATE() | | More about CURDATE Function |
| | CURTIME | CURTIME([Precision]) | | More about CURTIME Function |
| | CURRENT_DATE | CURRENT_DATE(-) | | More about CURRENT_DATE Function |
| | CURRENT_TIME | CURRENT_TIME([Precision]) | | More about CURRENT_TIME Function |
| | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP([Precision]) | | More about CURRENT_TIMESTAMP Function |
| | DATE | DATE(Date and Time) | | More about DATE Function |
| | DATE_ADD | DATE_ADD(Date, INTERVAL Number Unit) | | More about DATE_ADD Function |
| | DATE_SUB | DATE_SUB(Date, INTERVAL Number Unit) | | More about DATE_SUB Function |
| | FROM_DAYS | FROM_DAYS(Number of days) | | More about FROM_DAYS Function |
| | FROM_UNIXTIME | FROM_UNIXTIME(Unix timestamp [, Format]) | | More about FROM_UNIXTIME Function |
| | LOCALTIME | LOCALTIME([Precision]) | | More about LOCALTIME Function |
| | LOCALTIMESTAMP | LOCALTIMESTAMP([Precision]) | | More about LOCALTIMESTAMP Function |
| | MAKEDATE | MAKEDATE(Year, Day of year) | | More about MAKEDATE Function |
| | MAKETIME | MAKETIME(Hours, Minutes, Seconds) | | More about MAKETIME Function |
| | NOW | NOW([Precision]) | | More about NOW Function |
| | SEC_TO_TIME | SEC_TO_TIME(Seconds) | | More about SEC_TO_TIME Function |
| | SLEEP | SLEEP(Seconds) | | More about SLEEP Function |
| | STR_TO_DATE | STR_TO_DATE(String, Format) | | More about STR_TO_DATE Function |
| | SUBDATE | SUBDATE(Date, [INTERVAL] Number [Unit]) | | More about SUBDATE Function |
| | SUBTIME | SUBTIME(Date and Time, Deduction) | | More about SUBTIME Function |
| | SYSDATE | SYSDATE() | | More about SYSDATE Function |
| | TIME | TIME(Date and Time) | | More about TIME Function |
| | TIMEDIFF | TIMEDIFF(Date 1, Date 2) | | More about TIMEDIFF Function |
| | TIMESTAMPADD | TIMESTAMPADD(Unit, Number, Date) | | More about TIMESTAMPADD Function |
| | TIMESTAMPDIFF | TIMESTAMPDIFF(Unit, Date 1, Date 2) | | More about TIMESTAMPDIFF Function |
| | UTC_DATE | UTC_DATE() | | More about UTC_DATE Function |
| | UTC_TIME | UTC_TIME([Precision]) | | More about UTC_TIME Function |
| | UTC_TIMESTAMP | UTC_TIMESTAMP([Precision]) | | More about UTC_TIMESTAMP Function |
| | DAY | DAY(Date) | | More about DAY Function |
| | DAYOFMONTH | DAYOFMONTH(Date) | | More about DAYOFMONTH Function |
| | DAYOFWEEK | DAYOFWEEK(Date) | | More about DAYOFWEEK Function |
| | DAYOFYEAR | DAYOFYEAR(Date) | | More about DAYOFYEAR Function |
| | EXTRACT | EXTRACT(Unit FROM Date) | | More about EXTRACT Function |
| | HOUR | HOUR(Date and Time) | | More about HOUR Function |
| | MICROSECOND | MICROSECOND(Date and Time) | | More about MICROSECOND Function |
| | MINUTE | MINUTE(Date and Time) | | More about MINUTE Function |
| | MONTH | MONTH(Date) | | More about MONTH Function |
| | QUARTER | QUARTER(Date) | | More about QUARTER Function |
| | SECOND | SECOND(Date and Time) | | More about SECOND Function |
| | TIME_TO_SEC | TIME_TO_SEC(Time) | | More about TIME_TO_SEC Function |
| | TO_DAYS | TO_DAYS(Date) | | More about TO_DAYS Function |
| | TO_SECONDS | TO_SECONDS(Date and Time) | | More about TO_SECONDS Function |
| | UNIX_TIMESTAMP | UNIX_TIMESTAMP([Date and Time]) | | More about UNIX_TIMESTAMP Function |
| | WEEK | WEEK(Date [, Mode]) | | More about WEEK Function |
| | WEEKDAY | WEEKDAY(Date) | | More about WEEKDAY Function |
| | WEEKOFYEAR | WEEKOFYEAR(Date) | | More about WEEKOFYEAR Function |
| | YEAR | YEAR(Date) | | More about YEAR Function |
| | YEARWEEK | YEARWEEK(Date [, Mode]) | | More about YEARWEEK Function |
| | DAYNAME | DAYNAME(Date) | | More about DAYNAME Function |
| | DATE_FORMAT | DATE_FORMAT(Date and Time, Format) | | More about DATE_FORMAT Function |
| | GET_FORMAT | GET_FORMAT(Type, Format) | | More about GET_FORMAT Function |
| | MONTHNAME | MONTHNAME(Date) | | More about MONTHNAME Function |
| | TIME_FORMAT | TIME_FORMAT(Time, Format) | | More about TIME_FORMAT Function |
|
|
| | | More information about the TIMESTAMP SQL function: and |
|
|
|
|
