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 | 2019-12-02 19:29:41.103000+0000 |       2013-01-02 |       2019-12-02

(1 rows)

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com