You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I'm using the time_bucket function with both timezone and offset arguments, and I expected the offset to be applied after the time zone so that my 15 minute offset jumps ahead an hour and 15 minutes when DST occurs.
To clarify, in the screenshot below the "local_time" is in the Asia/Amman time zone, and when adding a 15 minute interval to "2021-03-25 23:45", the result should be "2021-03-26 01:00" instead of "2021-03-26 00:00" (an hour was skipped because of DST)
The time_bucket function doesn't take that into consideration because when running the below query I'd expect it to sum up the first 4 rows (all of March 25th, and the first row of March 26th because it's been offset 15 minutes).
SELECT
time_bucket('1 day'::interval, time,
"timezone" :='Asia/Amman',
"offset" :='15 minutes'::interval
) as day_bucket,
metric_id,
sum(value) as sum,
string_agg(value::text, ', ') as grouped_values
FROM metric_data
GROUP BY day_bucket, metric_id;
But instead the actual output is this:
Workaround
A workaround for this issue would be to actually not use the offset parameter on the time_bucket function and instead apply the offset directly on your timestamp column since that column is (hopefully) in UTC:
SELECT
time_bucket(
'1 day'::interval,
time-'15 minutes'::interval,
"timezone" :='Asia/Amman',
) as day_bucket,
metric_id,
sum(value) as sum,
string_agg(value::text, ', ') as grouped_values
FROM metric_data
GROUP BY day_bucket, metric_id;
Step 2. Run this query and notice how its output is not accurate
SELECT
time_bucket(
"bucket_width" :='1 day'::interval,
"ts" :=time,
"timezone" :='Asia/Amman',
"offset" :='15 minutes'::interval
) as day_bucket,
metric_id,
sum(value) as sum
FROM metric_data
GROUP BY day_bucket, metric_id;
The text was updated successfully, but these errors were encountered:
What type of bug is this?
Incorrect result
What subsystems and features are affected?
Other
What happened?
Description
I'm using the
time_bucket
function with bothtimezone
andoffset
arguments, and I expected the offset to be applied after the time zone so that my 15 minute offset jumps ahead an hour and 15 minutes when DST occurs.To clarify, in the screenshot below the "local_time" is in the
![image](https://private-user-images.githubusercontent.com/13931997/342110630-5325d6a3-d7da-4de6-aa7d-b21447891f9e.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjEyMjk3NTksIm5iZiI6MTcyMTIyOTQ1OSwicGF0aCI6Ii8xMzkzMTk5Ny8zNDIxMTA2MzAtNTMyNWQ2YTMtZDdkYS00ZGU2LWFhN2QtYjIxNDQ3ODkxZjllLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA3MTclMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNzE3VDE1MTczOVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWJkODIxOTM1NjVlM2ZiZDI5ZTU3NDMwYTA5Y2YwZTlkY2Y5ZGFlNzhiNDY5ZGQxNTc2OTVhNDE3YTA2MGM0YjUmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.qUh7BfdPBPzm4tevsnz0KM-izFTi4JsnklMae_5IiGU)
Asia/Amman
time zone, and when adding a 15 minute interval to "2021-03-25 23:45", the result should be "2021-03-26 01:00" instead of "2021-03-26 00:00" (an hour was skipped because of DST)The
time_bucket
function doesn't take that into consideration because when running the below query I'd expect it to sum up the first 4 rows (all of March 25th, and the first row of March 26th because it's been offset 15 minutes).But instead the actual output is this:
![image](https://private-user-images.githubusercontent.com/13931997/342114166-59a3f658-730f-426b-b216-1299bf4fbb91.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjEyMjk3NTksIm5iZiI6MTcyMTIyOTQ1OSwicGF0aCI6Ii8xMzkzMTk5Ny8zNDIxMTQxNjYtNTlhM2Y2NTgtNzMwZi00MjZiLWIyMTYtMTI5OWJmNGZiYjkxLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA3MTclMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNzE3VDE1MTczOVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTQ0MzAxMzY0OGQxZThkZTY2ZWQ5MjFkM2Y5NWRiMjU3YmMwOTFiYmE3OTViZDUwNGMzNmJhOWE0Yjk0NDg3N2UmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.ehQuTpS5zAybU12CdKe3IjkUivnRepqOcYUehCZ53T4)
Workaround
A workaround for this issue would be to actually not use the offset parameter on the time_bucket function and instead apply the offset directly on your timestamp column since that column is (hopefully) in UTC:
output:
![image](https://private-user-images.githubusercontent.com/13931997/342114660-c5cadd9a-bedf-4ea0-8cdb-afe995fa24ac.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjEyMjk3NTksIm5iZiI6MTcyMTIyOTQ1OSwicGF0aCI6Ii8xMzkzMTk5Ny8zNDIxMTQ2NjAtYzVjYWRkOWEtYmVkZi00ZWEwLThjZGItYWZlOTk1ZmEyNGFjLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA3MTclMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNzE3VDE1MTczOVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTM4Y2U2YWFlMDg2ODkxY2E2NzQwYmUzYWYzZmMzMjYzMTA4ZTQ5YjYzMTdiMDgxMWRhMmQ5ZTYxNTFjYmJjYzcmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.Z0ZdV9N3UqMPQGVO3x6BhHaK15MukQEiFYGVI0klLt0)
TimescaleDB version affected
2.5.1
PostgreSQL version used
14.2
What operating system did you use?
Windows 10 x64
What installation method did you use?
Docker
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace
No response
How can we reproduce the bug?
Step 1. Setup
Step 2. Run this query and notice how its output is not accurate
The text was updated successfully, but these errors were encountered: