docs/content/v2025.1/api/ycql/function_datetime.md
This section covers the set of YCQL built-in functions that work on the date and time data types: DATE, TIME, TIMESTAMP, or TIMEUUID.
Use these functions to return the current system date and time in UTC time zone.
DATE, TIME, or TIMESTAMP, respectively.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());
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
This function generates a new unique version 1 UUID (TIMEUUID).
TIMEUUID.ycqlsh:example> CREATE TABLE test_now (k INT PRIMARY KEY, v TIMEUUID);
ycqlsh:example> INSERT INTO test_now (k, v) VALUES (1, now());
ycqlsh:example> SELECT now() FROM test_now;
now()
---------------------------------------
b75bfaf6-4fe9-11e8-8839-6336e659252a
ycqlsh:example> SELECT v FROM test_now WHERE v < now();
v
---------------------------------------
71bb5104-4fe9-11e8-8839-6336e659252a
This function converts a timestamp or TIMEUUID to the corresponding date.
TIMESTAMP or TIMEUUID.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
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.
TIMESTAMP.TIMEUUID.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)
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)
This function generates corresponding (TIMEUUID) with maximum clock component so that it includes all regular
TIMEUUID with that timestamp when comparing with another TIMEUUID.
TIMESTAMP.TIMEUUID.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)
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)
This function converts a date or TIMEUUID to the corresponding timestamp.
DATE or TIMEUUID.TIMESTAMP.ycqlsh:example> CREATE TABLE test_totimestamp (k INT PRIMARY KEY, v TIMESTAMP);
ycqlsh:example> INSERT INTO test_totimestamp (k, v) VALUES (1, totimestamp(now()));
ycqlsh:example> SELECT totimestamp(now()) FROM test_totimestamp;
totimestamp(now())
---------------------------------
2018-05-04 22:32:56.966000+0000
ycqlsh:example> SELECT v FROM test_totimestamp WHERE v < totimestamp(now());
v
---------------------------------
2018-05-04 22:32:46.199000+0000
This function converts a TIMEUUID to the corresponding timestamp.
TIMEUUID.TIMESTAMP.ycqlsh:example> CREATE TABLE test_dateof (k INT PRIMARY KEY, v TIMESTAMP);
ycqlsh:example> INSERT INTO test_dateof (k, v) VALUES (1, dateof(now()));
ycqlsh:example> SELECT dateof(now()) FROM test_dateof;
dateof(now())
---------------------------------
2018-05-04 22:43:28.440000+0000
ycqlsh:example> SELECT v FROM test_dateof WHERE v < dateof(now());
v
---------------------------------
2018-05-04 22:43:18.626000+0000
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).
TIMEUUID, DATE or TIMESTAMP.BIGINT.ycqlsh:example> CREATE TABLE test_tounixtimestamp (k INT PRIMARY KEY, v BIGINT);
ycqlsh:example> INSERT INTO test_tounixtimestamp (k, v) VALUES (1, tounixtimestamp(now()));
ycqlsh:example> SELECT tounixtimestamp(now()) FROM test_tounixtimestamp;
tounixtimestamp(now())
------------------------
1525473993436
You can do this as follows:
ycqlsh:example> SELECT v from test_tounixtimestamp WHERE v < tounixtimestamp(now());
v
---------------
1525473942979
This function converts TIMEUUID or timestamp to a unix timestamp (which is equal to the number of millisecond since epoch Thursday, 1 January 1970).
TIMEUUID or type TIMESTAMP.BIGINT.ycqlsh:example> CREATE TABLE test_unixtimestampof (k INT PRIMARY KEY, v BIGINT);
ycqlsh:example> INSERT INTO test_unixtimestampof (k, v) VALUES (1, unixtimestampof(now()));
ycqlsh:example> SELECT unixtimestampof(now()) FROM test_unixtimestampof;
unixtimestampof(now())
------------------------
1525474361676
ycqlsh:example> SELECT v from test_unixtimestampof WHERE v < unixtimestampof(now());
v
---------------
1525474356781
This function generates a new unique version 4 UUID (UUID).
UUID.ycqlsh:example> CREATE TABLE test_uuid (k INT PRIMARY KEY, v UUID);
ycqlsh:example> INSERT INTO test_uuid (k, v) VALUES (1, uuid());
ycqlsh:example> SELECT v FROM test_uuid WHERE k = 1;
v
---------------------------------------
71bb5104-4fe9-11e8-8839-6336e659252a
ycqlsh:example> SELECT uuid() FROM test_uuid;
uuid()
--------------------------------------
12f91a52-ebba-4461-94c5-b73f0914284a