-1

I have this table which stores millions of rows of data. This data has a date that indicates when was the data entered. I store the data in NUMERIC schemas with EPOCH UNIX as the format. However, I wanted to convert them to human date (yyyy-mm-dd hh:mm:ss) and later sort them by date not queried date.

However, it took me so long to find a suitable way. Here's my attempt.

  1. I used SELECT CAST(DATE(timestamp) AS DATE) AS CURR_DT FROM dataset.table but it gave me this error:

    No matching signature for function DATE for argument types: NUMERIC. Supported signatures: DATE(TIMESTAMP, [STRING]); DATE(DATETIME); DATE(INT64, INT64, INT64) at [1:13]

  2. I used this method BigQuery: convert epoch to TIMESTAMP but still didn't fully understand

I'm a novice in coding so I hope you guys understand the situation. Thanks!

Iqbal Jurist
  • 43
  • 2
  • What don't you understand about that answer? It seems to answer your question which makes this a duplicate. Sorting by date and the Unix epoch is the same thing, by the way. – Gordon Linoff Nov 22 '20 at 12:43

2 Answers2

0

If I am understanding your question correctly you would like to take a numeric EPOCH time that is stored as an integer and convert it to a timestamp? If so you can use the following in BigQuery Standard SQL:

select TIMESTAMP_SECONDS(1606048220)

It gives the output of: 2020-11-22 12:30:20 UTC

Documentation

Daniel Zagales
  • 2,948
  • 2
  • 5
  • 18
0

If you only want the date component, then you would convert to a date after converting to a timestamp. Presumably you have seconds, so you would use TIMESTAMP_SECONDS() -- but there are similar functions for milliseconds and microseconds.

For just the date:

select date(timestamp_seconds(col))

Note that this removes the time component.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786