Lindorm:Time functions

Syntax

DATE_FORMAT(TIMESTAMP ts, STRING format)

Parameters

Example

Format the timestamp 2024-01-01 17:20:35 as at 17:20:35 on Jan 1st, 2024:

SELECT DATE_FORMAT('2024-01-01 17:20:35', 'at %T on %b %D, %Y');

Output:

+------------------------------+
| EXPR$0                       |
+------------------------------+
| at 17:20:35 on JAN 1st, 2024 |
+------------------------------+

Syntax

-- Convert a UNIX timestamp to a session-local timestamp.
FROM_UNIXTIME(Number seconds)

-- Convert and format the result.
-- Equivalent to: DATE_FORMAT(FROM_UNIXTIME(seconds), format)
FROM_UNIXTIME(Number seconds, STRING format)

The two-parameter form is equivalent to calling DATE_FORMAT on the result of the one-parameter form. The format parameter accepts the same specifiers as DATE_FORMAT. For details, see Format specifiers.

Parameters

Examples

Example 1: Convert a UNIX timestamp

Convert 10.1578 to a timestamp using the time zone of the current session:

SELECT FROM_UNIXTIME(10.1578);

Output:

+-------------------------+
| EXPR$0                  |
+-------------------------+
| 1970-01-01 08:00:10.158 |
+-------------------------+

Change the session time zone to +03:00 and convert again:

-- Change the session time zone.
SET @@time_zone='+03:00';

-- Convert using the new time zone.
SELECT FROM_UNIXTIME(10.1578);

Output:

+-------------------------+
| EXPR$0                  |
+-------------------------+
| 1970-01-01 03:00:10.158 |
+-------------------------+

Example 2: Convert and format

Convert 10.1578 and format the result using the time zone of the current session:

SELECT FROM_UNIXTIME(10.1578, 'at %T on %b %D, %Y');

Output:

+------------------------------+
| EXPR$0                       |
+------------------------------+
| at 08:00:10 on JAN 1st, 1970 |
+------------------------------+

Change the session time zone to +03:00 and format again:

-- Change the session time zone.
SET @@time_zone='+03:00';

-- Convert and format using the new time zone.
SELECT FROM_UNIXTIME(10.1578, 'at %T on %b %D, %Y');

Output:

+------------------------------+
| EXPR$0                       |
+------------------------------+
| at 03:00:10 on JAN 1st, 1970 |
+------------------------------+

Syntax

DATEDIFF(TIMESTAMP ts1, TIMESTAMP ts2)

Parameters

Examples

Example 1: ts1 is earlier than ts2

SELECT DATEDIFF('2024-01-01 15:30:00', '2024-01-02 00:59:59');

Output:

+--------+
| EXPR$0 |
+--------+
|     -1 |
+--------+

Example 2: ts1 is later than ts2

SELECT DATEDIFF('2024-01-05 15:30:00', '2024-01-02 00:59:59');

Output:

+--------+
| EXPR$0 |
+--------+
|      3 |
+--------+

Example 3: ts1 and ts2 fall on the same date

Time-of-day values differ, but the date is the same, so the result is 0:

SELECT DATEDIFF('2024-01-02 15:30:00', '2024-01-02 00:59:59');

Output:

+--------+
| EXPR$0 |
+--------+
|      0 |
+--------+

Syntax

UNIX_TIMESTAMP(ts)

Parameters

Examples

Create a table and insert sample data:

-- Create a table with TIMESTAMP and VARCHAR timestamp columns.
CREATE TABLE t1(p1 INT, c1 TIMESTAMP, c2 VARCHAR, c3 DOUBLE, PRIMARY KEY(p1));

-- Insert sample rows.
UPSERT INTO t1(p1, c1, c2, c3) VALUES
(1, '2025-01-01 08:30:00',          '2025-01-01 08:30:00',          '12.24'),
(2, '1970-01-01 08:00:00.123',      '1970-01-01 08:00:00.123',      '10.32'),
(3, '1970-01-01 08:00:10.13579',    '1970-01-01 08:00:10.13579',    '10.12'),
(4, '1970-01-01 08:00:10.123456789','1970-01-01 08:00:10.123456789','17.33');

Example 1: Query UNIX timestamps from a table

TIMESTAMP values (c1) retain 3 decimal places; VARCHAR values (c2) retain up to 6.

SELECT p1, UNIX_TIMESTAMP(c1), UNIX_TIMESTAMP(c2) FROM t1;

Output:

+----+------------+------------+
| p1 | EXPR$1     | EXPR$2     |
+----+------------+------------+
| 1  | 1735691400 | 1735691400 |
| 2  | 0.123      | 0.123      |
| 3  | 10.135     | 10.13579   |
| 4  | 10.123     | 10.123457  |
+----+------------+------------+

Example 2: Filter rows using UNIX_TIMESTAMP in a WHERE clause

Count rows where the UNIX timestamp of c1 exceeds 10:

SELECT COUNT(*) FROM t1 WHERE UNIX_TIMESTAMP(c1) > 10;

Output:

+--------+
| EXPR$0 |
+--------+
| 3      |
+--------+

Example 3: Write a UNIX_TIMESTAMP result to a DOUBLE column

Use UNIX_TIMESTAMP in a VALUES clause to write the result directly to the c3 DOUBLE column:

UPSERT INTO t1(p1, c3) VALUES(1, UNIX_TIMESTAMP('1970-01-01 08:00:00.123'));

To verify the result, run:

SELECT * FROM t1;