convert timestamp to current time format in SQL

Question

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?

Which dbms are you using? (When it comes to date/time, many products have their own functions - far from ANSI SQL compliant.) — jarlh, Jul 21 '21 at 17:09
Hope this helps - https://stackoverflow.com/questions/41756983/vertica-convert-date-format — abhinav pratap, Jul 21 '21 at 17:24
That would not work, but thanks for the assistance. I found a way and posted the answer. — trojan horse, Jul 21 '21 at 20:05

score 0 · Accepted Answer · answered Jul 21 '21 at 20:04

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.

marcothesane · Answer 2 · 2021-07-22T00:30:39.093

0

Just remove the 'Z' at the end of the incoming string and cast it to a timestamp with no second fractions.

WITH
indata(string) AS (
  SELECT '2021-07-20T18:40:36.368126186Z'
)
SELECT
  REPLACE(string,'Z','')::TIMESTAMP(0) AS resulting_timestamp_0
FROM indata;
 resulting_timestamp_0 
-----------------------
 2021-07-20 18:40:36
(1 row)

edited Jul 22 '21 at 00:30

answered Jul 22 '21 at 00:19

marcothesane

6,192
1
11
21

convert timestamp to current time format in SQL

2 Answers2

Linked