> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pixeltable.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Time Zones
> Work with timezone-aware timestamps in Pixeltable: store UTC, convert between zones, and run accurate date queries across regions.
This documentation page is also available as an interactive notebook. You can launch the notebook in
Kaggle or Colab, or download it for use with an IDE or local Jupyter installation, by clicking one of the
above links.
export const quartoRawHtml = [`
| dt |
note |
| 2024-08-09 23:00:00-07:00 |
No time zone specified (uses default) |
| 2024-08-09 23:00:00-07:00 |
Time zone America/Los_Angeles was specified explicitly |
| 2024-08-09 20:00:00-07:00 |
Time zone America/New_York was specified explicitly |
`, `
| dt |
dt_new_york |
note |
| 2024-08-09 23:00:00-07:00 |
2024-08-10 02:00:00-04:00 |
No time zone specified (uses default) |
| 2024-08-09 23:00:00-07:00 |
2024-08-10 02:00:00-04:00 |
Time zone America/Los_Angeles was specified explicitly |
| 2024-08-09 20:00:00-07:00 |
2024-08-09 23:00:00-04:00 |
Time zone America/New_York was specified explicitly |
`, `
| dt |
day_default |
day_eastern |
| 2024-08-09 23:00:00-07:00 |
9 |
10 |
| 2024-08-09 23:00:00-07:00 |
9 |
10 |
| 2024-08-09 20:00:00-07:00 |
9 |
9 |
`];
Because typical use cases involve datasets that span multiple time
zones, Pixeltable strives to be precise in how it handles time zone
arithmetic for datetimes.
Timestamps are always stored in the Pixeltable database in UTC, to
ensure consistency across datasets and deployments. Time zone
considerations therefore apply during insertion and retrieval of
timestamp data.
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
%pip install -qU pixeltable
```
### The default time zone
Every Pixeltable deployment has a **default time zone**. The default
time zone can be configured either by setting the `PIXELTABLE_TIME_ZONE`
environment variable, or by adding a `time-zone` entry to the
`[pixeltable]` section in `$PIXELTABLE_HOME/config.toml`. It must be a
valid [IANA Time
Zone](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones).
(See the [Pixeltable
Configuration](/platform/configuration) guide
for more details on configuration options.)
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
import os
os.environ['PIXELTABLE_TIME_ZONE'] = 'America/Los_Angeles'
```
If no time zone is configured, then Pixeltable will fall back on the
system time zone of the host on which it is running. **Because system
time zone is deployment-dependent, it is recommended that production
deployments configure a default time zone explicitly.**
As outlined in the [Python datetime
documentation](https://docs.python.org/3/library/datetime.html), a
Python `datetime` object may be either **naive** (no time zone) or
**aware** (equipped with an explicit time zone). Pixeltable will always
interpret naive `datetime` objects as belonging to the configured
default time zone.
### Insertion and retrieval
When a `datetime` is inserted into the database, it will be converted to
UTC and stored as an absolute timestamp. If the `datetime` has an
explicit time zone, Pixeltable will use that time zone for the
conversion; otherwise, Pixeltable will use the default time zone.
When a `datetime` is retrieved, it will always be retrieved in the
default time zone. To query in a different time zone, it is necessary to
do an explicit conversion; we’ll give an example of this in a moment.
Let’s first walk through a few examples that illustrate the default
behavior.
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
import pixeltable as pxt
pxt.drop_dir('tz_demo', force=True)
pxt.create_dir('tz_demo')
t = pxt.create_table(
'tz_demo/example', {'dt': pxt.Timestamp, 'note': pxt.String}
)
```
Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/Users/pjlb/.pixeltable/pgdata
Created directory 'tz\_demo'.
Created table 'example'.
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
from datetime import datetime, timezone
from zoneinfo import ZoneInfo
naive_dt = datetime(2024, 8, 9, 23, 0, 0)
explicit_dt = datetime(
2024, 8, 9, 23, 0, 0, tzinfo=ZoneInfo('America/Los_Angeles')
)
other_dt = datetime(
2024, 8, 9, 23, 0, 0, tzinfo=ZoneInfo('America/New_York')
)
t.insert(
[
{'dt': naive_dt, 'note': 'No time zone specified (uses default)'},
{
'dt': explicit_dt,
'note': 'Time zone America/Los_Angeles was specified explicitly',
},
{
'dt': other_dt,
'note': 'Time zone America/New_York was specified explicitly',
},
]
)
```
Inserting rows into \`example\`: 0 rows \[00:00, ? rows/s]
Inserting rows into \`example\`: 3 rows \[00:00, 433.04 rows/s]
Inserted 3 rows with 0 errors.
3 rows inserted, 3 values computed.
On retrieval, all timestamps are normalized to the default time zone,
regardless of how they were specified during insertion.
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
t.collect()
```
To represent timestamps in a different time zone, use the `astimezone`
method.
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
t.select(
t.dt, dt_new_york=t.dt.astimezone('America/New_York'), note=t.note
).collect()
```
### Timestamp methods and properties
The Pixeltable API exposes all the standard `datetime` methods and
properties from the Python library. Because retrieval uses the default
time zone, they are all relative to the default time zone unless
`astimezone` is used.
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
t.select(
t.dt,
day_default=t.dt.day,
day_eastern=t.dt.astimezone('America/New_York').day,
).collect()
```
Observe that the first two timestamps map to different dates depending
on the time zone, as expected.