Search code examples
sqlverticadatetime-conversion

convert timestamp to current time format in SQL

I have a timestamp column with value coming in this format:

2021-07-20T18:40:36.368126186Z

I want to convert the the result to make the timestamp clean as (in 24 hours format):

2021-07-20 18:40:36

I tried using the DATE field and it only gives me the date, and DATETIME function doesn't work on my database.

select timestamp,
DATE(timestamp),
CURRENT_TIMESTAMP(0) AT TIME ZONE 'America/New_York'  
from  table

-- This doesn't work for now as the DATE method removes the time.

The format I am choosing for output is similar CURRENT_TIMESTAMP(0) AT TIME ZONE 'America/New York'. How can can I get it. Can I use regex to just remove the values I don't need?

Solution

  • I was able to get the answer:

    SELECT timestamp AS original_ts, 
to_timestamp(REPLACE(REPLACE(timestamp,'T',' '),'Z',''),'YYYY-MM-DD HH:MI:SS') 
AS modified_ts from table

    I also tried CAST with TO_TIMESTAMP_TZ method but it didn't work as intended. If there are better solutions, I am happy to know but for now I got my answer.