Lindorm:Time functions

Syntax

DATE_FORMAT(TIMESTAMP ts, STRING format)

Parameters

The following table describes the supported format specifiers, output, and output description.

Examples

Formats the timestamp 2024-01-01 17:20:35 in the time zone of the current session as at 17:20:35 on Jan 1st, 2024.

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

Sample output:

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

The returned result is a string whose format is based on the format specifier. The string represents the timestamp in the time zone of the current session.

Syntax

• Converts Number-type data to a timestamp in the time zone of the current session.

  FROM_UNIXTIME (Number seconds) 

• Converts Number-type data to a timestamp in the time zone of the current session, and then formats the timestamp.

  FROM_UNIXTIME (Number seconds, STRING format)

Parameters

Examples

• Example 1:

  1. Converts the UNIX timestamp 10.1578 into the timestamp in the time zone of the current session.

     SELECT FROM_UNIXTIME(10.1578);

     Sample output:

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

  2. Changes the time_zone field to +03:00 and converts the UNIX timestamp 10.1578 into the timestamp in the new time zone.

     -- Change the time_zone field to +03:00.
     SET @@time_zone='+03:00';
     
     -- Convert to the timestamp in the new time zone.
     SELECT FROM_UNIXTIME(10.1578);

     Note

     Only the MySQL protocol allows you to change the time zone of a connection. For more information, see Use the MySQL protocol to develop applications (recommended) and Use a MySQL client to connect to and use LindormTable.

     Sample output:

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

• Example 2:

  1. Converts the UNIX timestamp 10.1578 into the timestamp in the time zone of the current session and then formats the timestamp.

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

     Sample output:

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

  2. Changes the time_zone field to +03:00, converts the UNIX timestamp 10.1578 into the timestamp in the new time zone, and then formats the timestamp.

     -- Change the time_zone field to +03:00.
     SET @@time_zone='+03:00';
     
     -- Convert to the timestamp in the new time zone.
     SELECT FROM_UNIXTIME(10.1578, 'at %T on %b %D, %Y');

     Sample output:

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

Syntax

DATEDIFF(TIMESTAMP ts1, TIMESTAMP ts2)

Parameters

Examples

• Example 1:

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

  Sample output:

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

• Example 2:

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

  Sample output:

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

• Example 3:

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

  Sample output:

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

Syntax

UNIX_TIMESTAMP (ts)

Parameters

Examples

Create a table named t1. Sample code:

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

-- Insert data into the table.
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 the UNIX timestamp corresponding to the timestamp or timestamp string.

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

  Sample 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: Calculate the number of records that meet the UNIX_TIMESTAMP(c1) > 10 condition.

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

  Sample output:

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

• Example 3: Write the result of the UNIX_TIMESTAMP function to the c3 column whose data type is DOUBLE.

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

  Verify the result

  You can execute the SELECT * FROM t1 statement to check whether the write operation was successful.