8000 Update time bucket docs to be more clear about time zones by atovpeko · Pull Request #4252 · timescale/docs · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Update time bucket docs to be more clear about time zones #4252

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 3 commits into from
Jul 7, 2025
Merged

Update time bucket docs to be more clear about time zones #4252

Changes from all commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
8684165
draft
atovpeko Jul 3, 2025
ca9271b
updates
atovpeko Jul 3, 2025
694378b
Merge branch 'latest' into 4190-docs-rfc-update-time_bucket-docs-to-b…
atovpeko Jul 7, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
29 changes: 15 additions & 14 deletions api/time_bucket.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -20,10 +20,11 @@ The `time_bucket` function is similar to the standard $PG `date_bin`
function. Unlike `date_bin`, it allows for arbitrary time intervals of months or
longer. The return value is the bucket's start time.

Note that daylight savings time boundaries means that the amount of data
aggregated into a bucket after such a cast can be irregular. For example, if the
`bucket_width` is 2 hours, the number of UTC hours bucketed by local time on
daylight savings time boundaries can be either three hours or one hour.
Buckets are aligned to start at midnight in UTC+0. The time bucket size (`bucket_width`) can be set as INTERVAL or INTEGER. For INTERVAL-type `bucket_width`, you can change the time zone with the optional `timezone` parameter. In this case, the buckets are realigned to start at midnight in the time zone you specify.

Note that during shifts to and from daylight savings, the amount of data
aggregated into the corresponding buckets can be irregular. For example, if the
`bucket_width` is 2 hours, the number of bucketed hours is either three hours or one hour.

## Required arguments for interval time inputs

Expand All @@ -38,11 +39,11 @@ bucket widths, but `1 month 1 day` and `3 months 2 weeks` are not.

## Optional arguments for interval time inputs

|Name|Type|Description|
|-|-|-|
|`timezone`|TEXT|The timezone for calculating bucket start and end times. Can only be used with `TIMESTAMPTZ`. Defaults to UTC.|
|`origin`|DATE, TIMESTAMP, or TIMESTAMPTZ|Buckets are aligned relative to this timestamp. Defaults to midnight on January 3, 2000, for buckets that don't include a month or year interval, and to midnight on January 1, 2000, for month, year, and century buckets.|
|`offset`|INTERVAL|The time interval to offset all time buckets by. A positive value shifts bucket start and end times later. A negative value shifts bucket start and end times earlier. `offset` must be surrounded with double quotes when used as a named argument, because it is a reserved key word in $PG.|
|Name|Type| Description |
|-|-|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|`timezone`|TEXT| The time zone for calculating bucket start and end times. Can only be used with `TIMESTAMPTZ`. Defaults to UTC+0. |
|`origin`|DATE, TIMESTAMP, or TIMESTAMPTZ| Buckets are aligned relative to this timestamp. Defaults to midnight on January 3, 2000, for buckets that don't include a month or year interval, and to midnight on January 1, 2000, for month, year, and century buckets. |
|`offset`|INTERVAL| The time interval to offset all time buckets by. A positive value shifts bucket start and end times later. A negative value shifts bucket start and end times earlier. `offset` must be surrounded with double quotes when used as a named argument, because it is a reserved key word in $PG. |

## Required arguments for integer time inputs

Expand All @@ -59,7 +60,7 @@ bucket widths, but `1 month 1 day` and `3 months 2 weeks` are not.

## Sample usage

Simple five minute averaging:
Simple five-minute averaging:

```sql
SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
Expand All @@ -79,7 +80,7 @@ ORDER BY five_min DESC LIMIT 10;
```

For rounding, move the alignment so that the middle of the bucket is at the
five minute mark, and report the middle of the bucket:
five-minute mark, and report the middle of the bucket:

```sql
SELECT time_bucket('5 minutes', time, '-2.5 minutes'::INTERVAL) + '2.5 minutes'
Expand All @@ -92,7 +93,7 @@ ORDER BY five_min DESC LIMIT 10;
In this example, add the explicit cast to ensure that $PG chooses the
correct function.

To shift the alignment of the buckets you can use the origin parameter passed as
To shift the alignment of the buckets, you can use the origin parameter passed as
a timestamp, timestamptz, or date type. This example shifts the start of the
week to a Sunday, instead of the default of Monday:

Expand All @@ -111,7 +112,7 @@ can be before, during, or after the data being analyzed. All buckets are
calculated relative to this origin. So, in this example, any Sunday could have
been used. Note that because `time < TIMESTAMPTZ '2018-01-03'` is used in this
example, the last bucket would have only 4 days of data. This cast to TIMESTAMP
converts the time to local time according to the server's timezone setting.
converts the time to local time according to the server's time zone setting.

```sql
SELECT time_bucket(INTERVAL '2 hours', timetz::TIMESTAMP)
Expand All @@ -122,7 +123,7 @@ ORDER BY five_min DESC LIMIT 10;
```

Bucket temperature values to calculate the average monthly temperature. Set the
timezone to 'Europe/Berlin' so bucket start and end times are aligned to
time zone to 'Europe/Berlin' so bucket start and end times are aligned to
midnight in Berlin.

```sql
Expand Down
0