How can I truncate (by hour) the result of my convert_timezone query.

SELECT 
convert_timezone('UTC', 'America/New_York', RECORD_TIMESTAMP) as Time
,date_trunc('HOUR', Time) as Truncated
FROM Date

Above works but I want to 'combine' it all on one line to give me a single result of truncated time.

CodePudding user response：

So if you have the SQL working uncombined, just stack the command

WITH date(RECORD_TIMESTAMP) as (
    select * from values
    ('2022-06-25 01:45:01'),
    ('2022-06-25 02:45:01')
)
SELECT 
    convert_timezone('UTC', 'America/New_York', RECORD_TIMESTAMP) as Time
    ,date_trunc('HOUR', Time) as Truncated

    ,date_trunc('HOUR',convert_timezone('UTC', 'America/New_York', RECORD_TIMESTAMP)) as truncated_Time

FROM Date

gives:

CodePudding user response：

Simply Combine both columns

SELECT
date_trunc('HOUR', convert_timezone('UTC', 'America/New_York', RECORD_TIMESTAMP)) as TruncatedTime
FROM Date