SQL截断并转换时区

发布于 2025-02-10 21:41:51 字数 238 浏览 1 评论 0原文

我如何（按小时）截断我的convert_timezone查询结果。

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

上面有效，但我想将其全部“将”全部“组合”到一行，以给我一个截断时间的结果。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

耶耶耶 2025-02-17 21:41:51

只需结合两个列

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

Simply Combine both columns

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

回复收藏 0 原文

↙温凉少女 2025-02-17 21:41:51

因此，如果您的SQL工作未完成，只需堆叠命令

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
    -- can just be stacked as
    ,date_trunc('HOUR',convert_timezone('UTC', 'America/New_York', RECORD_TIMESTAMP)) as truncated_Time

FROM Date

提供：

截断	的	truncated_time
2022-06-24 21：45：01.000	：00.000 2022-06-24 21：00.0006-24 21：00.00.00.000 2022--2022---2022---2022---2022--022--2022--022--022--022--022--022--022--022--022--2022--2022--0	2022--022--00.000
2022-06-24 21 06-24 22:45:01.000	2022-06-24 22:00:00.000	2022-06-24 22:00:00.000

Or just written as the stacked line:

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

TRUNCATED_TIME
2022-06-24 21:00:00.000
2022-06 -24 22：00：00.000

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
    -- can just be stacked as
    ,date_trunc('HOUR',convert_timezone('UTC', 'America/New_York', RECORD_TIMESTAMP)) as truncated_Time

FROM Date

gives:

TIME	TRUNCATED	TRUNCATED_TIME
2022-06-24 21:45:01.000	2022-06-24 21:00:00.000	2022-06-24 21:00:00.000
2022-06-24 22:45:01.000	2022-06-24 22:00:00.000	2022-06-24 22:00:00.000

Or just written as the stacked line:

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