2

I need to change timestamp like this:

1519152103659000     
1519152113786000     
1519152118754001     
1519152118972002     
1519152119026003

to a date in sql. This timestamp is a column in my table. I use timestamp command but I have a error:

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

Thanks!!

  • 1
    What do the values of this timestamp represent? I don't think there is a system function that will do it without some parsing. – Jacob H Feb 21 '18 at 16:47
  • I thinks is time in seconds since 1/1/1970 –  Feb 21 '18 at 16:49
  • 1
    Ah unix. Try here: https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server The second answer is pretty straightforward. – Jacob H Feb 21 '18 at 16:50
  • 1
    Possible duplicate of [How can I convert bigint (UNIX timestamp) to datetime in SQL Server?](https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server) – Mark Benningfield Feb 21 '18 at 16:52
  • Possible duplicate of [How to convert SQL Server's timestamp column to datetime format](https://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format) – William Perron Feb 21 '18 at 16:59
  • 1
    TIMESTAMP is a datatype in sql server which oddly has absolutely nothing to do with a calendar. – Sean Lange Feb 21 '18 at 16:59
  • The timestamp data type is just an incrementing number and does not preserve a date or a time. Taken from https://msdn.microsoft.com/en-us/library/ms182776%28v=SQL.90%29.aspx – SS_DBA Feb 21 '18 at 17:19
  • Are you using BigQuery? It's not very clear from the question and the tags. – Daria Feb 21 '18 at 17:36

2 Answers2

6

Below example is for BigQuery StandardSQL 

#standardSQL
WITH t AS (
  SELECT 1519152103659000 AS ts UNION ALL
  SELECT 1519152113786000 UNION ALL
  SELECT 1519152118754001 UNION ALL
  SELECT 1519152118972002 UNION ALL
  SELECT 1519152119026003 
)
SELECT 
  ts AS timestamp_in_microseconds_as_int64,  
  TIMESTAMP_MICROS(ts) AS timestamp_as_timestamp,
  DATE(TIMESTAMP_MICROS(ts)) AS dt
FROM t

with result as 

Row timestamp_in_microseconds_as_int64  timestamp_as_timestamp          dt   
1   1519152103659000                    2018-02-20 18:41:43.659 UTC     2018-02-20   
2   1519152113786000                    2018-02-20 18:41:53.786 UTC     2018-02-20   
3   1519152118754001                    2018-02-20 18:41:58.754 UTC     2018-02-20   
4   1519152118972002                    2018-02-20 18:41:58.972 UTC     2018-02-20   
5   1519152119026003                    2018-02-20 18:41:59.026 UTC     2018-02-20
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
2

Simply use DATEADD like this whenever you want your timestamp converted into a datetime string:

DATEADD(s, timestamp_column_name_here, '1970-01-01')

The column in question should contain timestamp strings (so should not be a timestamp-type one).

sliute
  • 101
  • 5