6

I want to convert a readable timestamp to UNIX time.

For example: I want to convert 2018-08-24 18:42:16 to 1535136136000.

Here is my syntax: 

    TO_UNIXTIME('2018-08-24 06:42:16') new_year_ut

My error is: 

   SYNTAX_ERROR: line 1:77: Unexpected parameters (varchar(19)) for function to_unixtime. Expected: to_unixtime(timestamp) , to_unixtime(timestamp with time zone)
Manfred Moser
  • 29,539
  • 13
  • 92
  • 123
noobeerp
  • 417
  • 2
  • 6
  • 11

1 Answers1

10

You need to wrap the varchar in a CAST to timestamp:

to_unixtime(CAST('2018-08-24 06:42:16' AS timestamp)) -- note: returns a double

If your timestamp value doesn't have fraction of second (or you are not interested in it), you can cast to bigint to have integral result:

CAST(to_unixtime(CAST('2018-08-24 06:42:16' AS timestamp)) AS BIGINT)

If your readable timestamp value is a string in different format than the above, you would need to use date_parse or parse_datetime for the conversion. See https://trino.io/docs/current/functions/datetime.html for more information.

Note: when dealing with timestamp values, please keep in mind that: https://github.com/trinodb/trino/issues/37

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
  • 3
    Thank you @PiotrFindeisen! It is not giving me errors but it is returning ` 1.535092936E9` and what I want is `1535136136000` – noobeerp Aug 24 '18 at 21:01
  • 3
    `TO_UNIXTIME` returns a `double` (as timestamp has millisecond precision), so you need a cast to bigint. Updated. – Piotr Findeisen Aug 25 '18 at 05:52