UUID and timeuuid column

The uuid() function takes no parameters and generates a random Type 4 UUID suitable for use in INSERT or SET statements.

Several timeuuid() functions are designed for use with the timeuuid() type:

dateOf()

Used in a SELECT clause, this function extracts the timestamp of a timeuuid column in a result set. This function returns the extracted timestamp as a date. Use unixTimestampOf() to get a raw timestamp.
now()

In the coordinator node, generates a new unique timeuuid in milliseconds when the statement is executed. The timestamp portion of the timeuuid conforms to the UTC (Universal Time) standard. This method is useful for inserting values. The value returned by now() is guaranteed to be unique.
minTimeuuid() and maxTimeuuid()

Returns a UUID-like result given a conditional time component as an argument. For example:
```
SELECT a, b, toDate(c), toDate(d) FROM cycling.sample_times
  WHERE c > maxTimeuuid('2013-01-01 00:01+0000')
    AND c < minTimeuuid('2013-01-02 10:00+0000')
ALLOW FILTERING;
```
The min/maxTimeuuid example selects all rows where the timeuuid column, t, is strictly later than 2013-01-01 00:01+0000 but strictly earlier than 2013-01-02 10:00+0000. The t >= maxTimeuuid('2013-01-01 00:01+0000') does not select a timeuuid generated exactly at 2013-01-01 00:01+0000 and is essentially equivalent to t > maxTimeuuid('2013-01-01 00:01+0000').

The values returned by minTimeuuid and maxTimeuuid functions are not true UUID`s in that the values do not conform to the Time-Based `UUID generation process specified by the RFC 4122. The results of these functions are deterministic, unlike the now() function.
unixTimestampOf()

Used in a SELECT clause, this functions extracts the timestamp in milliseconds of a timeuuid column in a result set. Returns the value as a raw, 64-bit integer timestamp.

The following functions support some additional timeuuid and timestamp functions to manipulate dates. The functions can be used in INSERT, UPDATE, and SELECT statements.

toDate(timeuuid)

Converts timeuuid to date in YYYY-MM-DD format.
toTimestamp(timeuuid)

Converts timeuuid to timestamp format.
toUnixTimestamp(timeuuid)

Converts timeuuid to UNIX timestamp format.
toDate(timestamp)

Converts timestamp to date in YYYY-MM-DD format.
toUnixTimestamp(timestamp)

Converts timestamp to UNIX timestamp format.
toTimestamp(date)

Converts date to timestamp format.
toUnixTimestamp(date)

Converts date to UNIX timestamp format.

An example of the new functions creates a table and inserts various time-related values:

CREATE TABLE IF NOT EXISTS cycling.sample_times (
  a int,
  b timestamp,
  c timeuuid,
  d bigint,
  PRIMARY KEY (a)
);

INSERT INTO cycling.sample_times (
  a, b, c, d
) VALUES (
  1, toUnixTimestamp(now()), now(), toTimestamp(now())
);

INSERT INTO cycling.sample_times (
  a, b, c, d
) VALUES (
  2, toUnixTimestamp(now()), maxTimeuuid('2013-01-02 00:01+0000'), toTimestamp(now())
);

INSERT INTO cycling.sample_times (
  a, b, c, d
) VALUES (
  3, toUnixTimestamp(now()), maxTimeuuid('2013-01-03 00:03+0000'), toTimestamp(now())
);

INSERT INTO cycling.sample_times (
  a, b, c, d
) VALUES (
  4, toUnixTimestamp(now()), maxTimeuuid('2013-01-04 00:05+0000'), toTimestamp(now())
);

Select data and convert it to a new format:

SELECT a, b, toDate(c), toDate(d) FROM cycling.sample_times
  WHERE c > maxTimeuuid('2013-01-01 00:01+0000')
    AND c < minTimeuuid('2013-01-02 10:00+0000')
ALLOW FILTERING;

 a | b                               | system.todate(c) | system.todate(d)
---+---------------------------------+------------------+------------------
 2 | 2024-07-02 23:59:00.071000+0000 |       2013-01-02 |       2024-07-02

(1 rows)

UUID and timeuuid column

Was this helpful?

Give Feedback