How do I convert Unix timestamp to date in CockroachDB?

1
votes

I've looked everywhere for this but can't find exactly what I need. I have a table that has a column in it containing the unix timestamp for the date of a transaction. In MySQL, I can convert this very easily but have been having a problem doing this in CockroachDB.

What I am looking for is simply to be able to query like the following would be done in MySQL: 

SELECT
  DATE(FROM_UNIXTIME(transaction_timestamp)),
  COUNT(id)
FROM 
  purchases

Any ideas?

1
timestampunix-timestampcockroachdb

1 Answers

3
votes

It depends on the datatype of transaction_timestamp.

If it's a TIMESTAMP, you can convert it to DATE using one of:

  • SELECT transaction_timestamp :: DATE
  • SELECT CAST(transaction_timestamp AS DATE)

If it's just an INT (or DECIMAL or FLOAT), you'll first need to cast it into a timestamp.

  • SELECT transaction_timestamp :: TIMESTAMP :: DATE
  • SELECT CAST(CAST(transaction_timestamp AS TIMESTAMP) AS DATE)

DO NOT cast directly from INT/DECIMAL/FLOAT to DATE, that interprets the value as the number of days since the Unix epoch.

The CRDB docs have more information about casting as well as casting rules for each data type (TIMESTAMP and DATE).