Date and time functions

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

This section covers the set of YCQL built-in functions that work on the date and time data types: DATE, TIME, TIMESTAMP, or TIMEUUID.

currentdate(), currenttime(), and currenttimestamp()

Use these functions to return the current system date and time in UTC time zone.

  • They take no arguments.
  • The return value is a DATE, TIME, or TIMESTAMP, respectively.

Examples

Insert values using currentdate(), currenttime(), and currenttimestamp()

ycqlsh:example> CREATE TABLE test_current (k INT PRIMARY KEY, d DATE, t TIME, ts TIMESTAMP);

ycqlsh:example> INSERT INTO test_current (k, d, t, ts) VALUES (1, currentdate(), currenttime(), currenttimestamp());

Comparison using currentdate() and currenttime()

ycqlsh:example> SELECT * FROM test_current WHERE d = currentdate() and t < currenttime();

 k | d          | t                  | ts
---+------------+--------------------+---------------------------------
 1 | 2018-10-09 | 18:00:41.688216000 | 2018-10-09 18:00:41.688000+0000

now()

This function generates a new unique version 1 UUID (TIMEUUID).

  • It takes in no arguments.
  • The return value is a TIMEUUID.

Examples

Insert values using now()

ycqlsh:example> CREATE TABLE test_now (k INT PRIMARY KEY, v TIMEUUID);

ycqlsh:example> INSERT INTO test_now (k, v) VALUES (1, now());

Select using now()

ycqlsh:example> SELECT now() FROM test_now;

 now()
---------------------------------------
 b75bfaf6-4fe9-11e8-8839-6336e659252a

Comparison using now()

ycqlsh:example> SELECT v FROM test_now WHERE v < now();

 v
---------------------------------------
 71bb5104-4fe9-11e8-8839-6336e659252a

todate()

This function converts a timestamp or TIMEUUID to the corresponding date.

  • It takes in an argument of type TIMESTAMP or TIMEUUID.
  • The return value is a DATE.
ycqlsh:example> CREATE TABLE test_todate (k INT PRIMARY KEY, ts TIMESTAMP);

ycqlsh:example> INSERT INTO test_todate (k, ts) VALUES (1, currenttimestamp());

ycqlsh:example> SELECT todate(ts) FROM test_todate;

 todate(ts)
------------
 2018-10-09

minTimeUUID()

This function generates corresponding (TIMEUUID) with minimum node/clock component so that it includes all regular TIMEUUID with that timestamp when comparing with another TIMEUUID.

  • It takes in an argument of type TIMESTAMP.
  • The return value is a TIMEUUID.

Examples

Insert values using now()

ycqlsh:example> CREATE TABLE test_min (k INT PRIMARY KEY, v TIMEUUID);

ycqlsh:example> INSERT INTO test_min (k, v) VALUES (1, now());

ycqlsh:ybdemo> select k, v, totimestamp(v) from test_min;

 k | v                                    | totimestamp(v)
---+--------------------------------------+---------------------------------
 1 | dc79344c-cb79-11ec-915e-5219fa422f77 | 2022-05-04 07:14:39.205000+0000

(1 rows)

Select using minTimeUUID()

ycqlsh:ybdemo> SELECT * FROM test_min WHERE v > minTimeUUID('2022-04-04 13:42:00+0000');

 k | v
---+--------------------------------------
 1 | dc79344c-cb79-11ec-915e-5219fa422f77

(1 rows)

maxTimeUUID()

This function generates corresponding (TIMEUUID) with maximum clock component so that it includes all regular TIMEUUID with that timestamp when comparing with another TIMEUUID.

  • It takes in an argument of type TIMESTAMP.
  • The return value is a TIMEUUID.

Examples

Insert values using now()

ycqlsh:example> CREATE TABLE test_max (k INT PRIMARY KEY, v TIMEUUID);

ycqlsh:example> INSERT INTO test_max (k, v) VALUES (1, now());

ycqlsh:ybdemo> SELECT k, v, totimestamp(v) from test_max;

 k | v                                    | totimestamp(v)
---+--------------------------------------+---------------------------------
 1 | e9261bcc-395a-11eb-9edc-112a0241eb23 | 2020-12-08 13:40:18.636000+0000

(1 rows)

Select using maxTimeUUID()

ycqlsh:ybdemo> SELECT * FROM test_max WHERE v <= maxTimeUUID('2022-05-05 00:34:32+0000');

 k | v
---+--------------------------------------
 1 | dc79344c-cb79-11ec-915e-5219fa422f77

(1 rows)

totimestamp()

This function converts a date or TIMEUUID to the corresponding timestamp.

  • It takes in an argument of type DATE or TIMEUUID.
  • The return value is a TIMESTAMP.

Examples

Insert values using totimestamp()

ycqlsh:example> CREATE TABLE test_totimestamp (k INT PRIMARY KEY, v TIMESTAMP);

ycqlsh:example> INSERT INTO test_totimestamp (k, v) VALUES (1, totimestamp(now()));

Select using totimestamp()

ycqlsh:example> SELECT totimestamp(now()) FROM test_totimestamp;

 totimestamp(now())
---------------------------------
 2018-05-04 22:32:56.966000+0000

Comparison using totimestamp()

ycqlsh:example> SELECT v FROM test_totimestamp WHERE v < totimestamp(now());

 v
---------------------------------
 2018-05-04 22:32:46.199000+0000

dateof()

This function converts a TIMEUUID to the corresponding timestamp.

  • It takes in an argument of type TIMEUUID.
  • The return value is a TIMESTAMP.

Examples

Insert values using dateof()

ycqlsh:example> CREATE TABLE test_dateof (k INT PRIMARY KEY, v TIMESTAMP);

ycqlsh:example> INSERT INTO test_dateof (k, v) VALUES (1, dateof(now()));

Select using dateof()

ycqlsh:example> SELECT dateof(now()) FROM test_dateof;

 dateof(now())
---------------------------------
 2018-05-04 22:43:28.440000+0000

Comparison using dateof()

ycqlsh:example> SELECT v FROM test_dateof WHERE v < dateof(now());

 v
---------------------------------
 2018-05-04 22:43:18.626000+0000

tounixtimestamp()

This function converts TIMEUUID, date, or timestamp to a UNIX timestamp (which is equal to the number of millisecond since epoch Thursday, 1 January 1970).

  • It takes in an argument of type TIMEUUID, DATE or TIMESTAMP.
  • The return value is a BIGINT.

Examples

Insert values using tounixtimestamp()

ycqlsh:example> CREATE TABLE test_tounixtimestamp (k INT PRIMARY KEY, v BIGINT);

ycqlsh:example> INSERT INTO test_tounixtimestamp (k, v) VALUES (1, tounixtimestamp(now()));

Select using tounixtimestamp()

ycqlsh:example> SELECT tounixtimestamp(now()) FROM test_tounixtimestamp;

 tounixtimestamp(now())
------------------------
          1525473993436

Comparison using tounixtimestamp()

You can do this as follows:

ycqlsh:example> SELECT v from test_tounixtimestamp WHERE v < tounixtimestamp(now());

 v
---------------
 1525473942979

unixtimestampof()

This function converts TIMEUUID or timestamp to a unix timestamp (which is equal to the number of millisecond since epoch Thursday, 1 January 1970).

  • It takes in an argument of type TIMEUUID or type TIMESTAMP.
  • The return value is a BIGINT.

Examples

Insert values using unixtimestampof()

ycqlsh:example> CREATE TABLE test_unixtimestampof (k INT PRIMARY KEY, v BIGINT);

ycqlsh:example> INSERT INTO test_unixtimestampof (k, v) VALUES (1, unixtimestampof(now()));

Select using unixtimestampof()

ycqlsh:example> SELECT unixtimestampof(now()) FROM test_unixtimestampof;

 unixtimestampof(now())
------------------------
          1525474361676

Comparison using unixtimestampof()

ycqlsh:example> SELECT v from test_unixtimestampof WHERE v < unixtimestampof(now());

 v
---------------
 1525474356781

uuid()

This function generates a new unique version 4 UUID (UUID).

  • It takes in no arguments.
  • The return value is a UUID.

Examples

Insert values using uuid()

ycqlsh:example> CREATE TABLE test_uuid (k INT PRIMARY KEY, v UUID);

ycqlsh:example> INSERT INTO test_uuid (k, v) VALUES (1, uuid());

Selecting the inserted uuid value

ycqlsh:example> SELECT v FROM test_uuid WHERE k = 1;

 v
---------------------------------------
 71bb5104-4fe9-11e8-8839-6336e659252a

Select using uuid()

ycqlsh:example> SELECT uuid() FROM test_uuid;

 uuid()
--------------------------------------
 12f91a52-ebba-4461-94c5-b73f0914284a

See also