SQL Date Functions Overview: TO_TIMESTAMP, LOCALTIMESTAMP, NOW, CURRENT_TIMESTAMP, DATE_FORMAT, UNIX_TIMESTAMP

TO_TIMESTAMP

Syntax

TIMESTAMP TO_TIMESTAMP(BIGINT time)
TIMESTAMP TO_TIMESTAMP(VARCHAR date)
TIMESTAMP TO_TIMESTAMP(VARCHAR date, VARCHAR format)

Parameters

Accepts either a BIGINT representing a Unix timestamp or a VARCHAR containing a date string, optionally with a format string.

Function description

Converts a BIGINT or VARCHAR date into a TIMESTAMP value.

Example

SELECT TO_TIMESTAMP(timestamp1) AS var1,
       TO_TIMESTAMP(timestamp2) AS var2,
       TO_TIMESTAMP(timestamp3, 'yyyyMMddHHmmss') AS var3
FROM T1;

LOCALTIMESTAMP

Syntax timestamp LOCALTIMESTAMP Parameters

None.

Function description

Returns the current system timestamp.

Example

SELECT LOCALTIMESTAMP AS `result`
FROM T1;

NOW

Syntax

BIGINT NOW()
BIGINT NOW(a)

Parameters

If called without arguments, returns the current epoch time in seconds.

If an INT argument a is provided, returns the epoch time offset by a seconds (e.g., NOW(100) returns current time + 100 seconds). If a is NULL, the result is NULL.

Example

SELECT NOW() AS now,
       NOW(100) AS now_100,
       NOW(a) AS now_null
FROM T1;

CURRENT_TIMESTAMP

Syntax TIMESTAMP CURRENT_TIMESTAMP Function description

Returns the current UTC timestamp in milliseconds.

Example

SELECT CURRENT_TIMESTAMP AS var1
FROM T1;

DATE_FORMAT

Syntax

VARCHAR DATE_FORMAT(TIMESTAMP time, VARCHAR to_format)
VARCHAR DATE_FORMAT(VARCHAR date, VARCHAR to_format)
VARCHAR DATE_FORMAT(VARCHAR date, VARCHAR from_format, VARCHAR to_format)

Parameters

The first argument is the source date (TIMESTAMP or VARCHAR). The optional from_format specifies the source format (default yyyy-MM-dd hh:mm:ss). The second/third argument defines the target format.

Function description

Converts a date string from its original format to a new format, returning the result as a VARCHAR. Returns NULL if any argument is NULL or parsing fails.

UNIX_TIMESTAMP

Syntax

BIGINT UNIX_TIMESTAMP()
BIGINT UNIX_TIMESTAMP(VARCHAR date)
BIGINT UNIX_TIMESTAMP(TIMESTAMP timestamp)
BIGINT UNIX_TIMESTAMP(VARCHAR date, VARCHAR format)

Parameters

When called without arguments, returns the current epoch time in seconds (same semantics as NOW()). With a date or timestamp argument, converts it to a BIGINT epoch value; returns NULL on NULL input or parsing error.

Example

SELECT UNIX_TIMESTAMP() AS big1,
       UNIX_TIMESTAMP(nullstr) AS big2
FROM T1;