{ "cells": [ { "cell_type": "markdown", "id": "0532f44e-eba0-447f-a0da-36f498048d15", "metadata": {}, "source": [ "# Time Zones\n", "\n", "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.\n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 1, "id": "5aa13928-7c00-4a97-b4f1-41a3bc3b19c3", "metadata": { "execution": { "iopub.execute_input": "2025-12-12T02:37:07.252035Z", "iopub.status.busy": "2025-12-12T02:37:07.251845Z", "iopub.status.idle": "2025-12-12T02:37:09.766409Z", "shell.execute_reply": "2025-12-12T02:37:09.765845Z" } }, "outputs": [], "source": [ "%pip install -qU pixeltable" ] }, { "cell_type": "markdown", "id": "41ead473-d299-46f8-99a9-57c3e2696ae3", "metadata": {}, "source": [ "### The default time zone\n", "\n", "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).\n", "\n", "(See the [Pixeltable Configuration](https://docs.pixeltable.com/platform/configuration) guide for more details on configuration options.)" ] }, { "cell_type": "code", "execution_count": 2, "id": "6472596c-98ec-48dd-9d35-a4879eea131e", "metadata": { "execution": { "iopub.execute_input": "2025-12-12T02:37:09.769054Z", "iopub.status.busy": "2025-12-12T02:37:09.768898Z", "iopub.status.idle": "2025-12-12T02:37:09.770947Z", "shell.execute_reply": "2025-12-12T02:37:09.770582Z" } }, "outputs": [], "source": [ "import os\n", "\n", "os.environ['PIXELTABLE_TIME_ZONE'] = 'America/Los_Angeles'" ] }, { "cell_type": "markdown", "id": "d0fc99cc-09cb-49fc-8c8a-2d80acfd0a74", "metadata": {}, "source": [ "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.__\n", "\n", "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." ] }, { "cell_type": "markdown", "id": "ff720908-8741-488d-826f-51bded809518", "metadata": {}, "source": [ "### Insertion and retrieval" ] }, { "cell_type": "markdown", "id": "5f3ab151-371e-4928-b346-5d86e53a9277", "metadata": {}, "source": [ "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.\n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 3, "id": "ae3f595b-fd0b-45bd-b08c-2ec1b83fa5d8", "metadata": { "execution": { "iopub.execute_input": "2025-12-12T02:37:09.773498Z", "iopub.status.busy": "2025-12-12T02:37:09.773333Z", "iopub.status.idle": "2025-12-12T02:37:11.179633Z", "shell.execute_reply": "2025-12-12T02:37:11.179282Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/Users/pjlb/.pixeltable/pgdata\n", "Created directory 'tz_demo'.\n", "Created table 'example'.\n" ] } ], "source": [ "import pixeltable as pxt\n", "\n", "pxt.drop_dir('tz_demo', force=True)\n", "pxt.create_dir('tz_demo')\n", "t = pxt.create_table(\n", " 'tz_demo/example', {'dt': pxt.Timestamp, 'note': pxt.String}\n", ")" ] }, { "cell_type": "code", "execution_count": 4, "id": "e315a153-7401-467f-9f20-d4b273a417f4", "metadata": { "execution": { "iopub.execute_input": "2025-12-12T02:37:11.182045Z", "iopub.status.busy": "2025-12-12T02:37:11.181825Z", "iopub.status.idle": "2025-12-12T02:37:11.922410Z", "shell.execute_reply": "2025-12-12T02:37:11.921683Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\r", "Inserting rows into `example`: 0 rows [00:00, ? rows/s]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "\r", "Inserting rows into `example`: 3 rows [00:00, 433.04 rows/s]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "Inserted 3 rows with 0 errors.\n" ] }, { "data": { "text/plain": [ "3 rows inserted, 3 values computed." ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from datetime import datetime, timezone\n", "from zoneinfo import ZoneInfo\n", "\n", "naive_dt = datetime(2024, 8, 9, 23, 0, 0)\n", "explicit_dt = datetime(\n", " 2024, 8, 9, 23, 0, 0, tzinfo=ZoneInfo('America/Los_Angeles')\n", ")\n", "other_dt = datetime(\n", " 2024, 8, 9, 23, 0, 0, tzinfo=ZoneInfo('America/New_York')\n", ")\n", "\n", "t.insert(\n", " [\n", " {'dt': naive_dt, 'note': 'No time zone specified (uses default)'},\n", " {\n", " 'dt': explicit_dt,\n", " 'note': 'Time zone America/Los_Angeles was specified explicitly',\n", " },\n", " {\n", " 'dt': other_dt,\n", " 'note': 'Time zone America/New_York was specified explicitly',\n", " },\n", " ]\n", ")" ] }, { "cell_type": "markdown", "id": "e84a8be7-770c-4b7e-84dc-96c9c72be248", "metadata": {}, "source": [ "On retrieval, all timestamps are normalized to the default time zone, regardless of how they were specified during insertion." ] }, { "cell_type": "code", "execution_count": 5, "id": "ef1f573a-638b-47e1-9420-17d88b2b1bfb", "metadata": { "execution": { "iopub.execute_input": "2025-12-12T02:37:11.924794Z", "iopub.status.busy": "2025-12-12T02:37:11.924363Z", "iopub.status.idle": "2025-12-12T02:37:11.938802Z", "shell.execute_reply": "2025-12-12T02:37:11.938363Z" } }, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dtnote
2024-08-09 23:00:00-07:00No time zone specified (uses default)
2024-08-09 23:00:00-07:00Time zone America/Los_Angeles was specified explicitly
2024-08-09 20:00:00-07:00Time zone America/New_York was specified explicitly
" ], "text/plain": [ " dt note\n", "0 2024-08-09 23:00:00-07:00 No time zone specified (uses default)\n", "1 2024-08-09 23:00:00-07:00 Time zone America/Los_Angeles was specified ex...\n", "2 2024-08-09 20:00:00-07:00 Time zone America/New_York was specified expli..." ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "t.collect()" ] }, { "cell_type": "markdown", "id": "2f5a1cd6-6e27-4ea6-9de0-1bf50130c557", "metadata": {}, "source": [ "To represent timestamps in a different time zone, use the `astimezone` method." ] }, { "cell_type": "code", "execution_count": 6, "id": "bbe2c4b4-36a5-4153-8caf-dbc2939ebc3b", "metadata": { "execution": { "iopub.execute_input": "2025-12-12T02:37:11.940740Z", "iopub.status.busy": "2025-12-12T02:37:11.940530Z", "iopub.status.idle": "2025-12-12T02:37:11.954415Z", "shell.execute_reply": "2025-12-12T02:37:11.954034Z" } }, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dtdt_new_yorknote
2024-08-09 23:00:00-07:002024-08-10 02:00:00-04:00No time zone specified (uses default)
2024-08-09 23:00:00-07:002024-08-10 02:00:00-04:00Time zone America/Los_Angeles was specified explicitly
2024-08-09 20:00:00-07:002024-08-09 23:00:00-04:00Time zone America/New_York was specified explicitly
" ], "text/plain": [ " dt dt_new_york \\\n", "0 2024-08-09 23:00:00-07:00 2024-08-10 02:00:00-04:00 \n", "1 2024-08-09 23:00:00-07:00 2024-08-10 02:00:00-04:00 \n", "2 2024-08-09 20:00:00-07:00 2024-08-09 23:00:00-04:00 \n", "\n", " note \n", "0 No time zone specified (uses default) \n", "1 Time zone America/Los_Angeles was specified ex... \n", "2 Time zone America/New_York was specified expli... " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "t.select(\n", " t.dt, dt_new_york=t.dt.astimezone('America/New_York'), note=t.note\n", ").collect()" ] }, { "cell_type": "markdown", "id": "6b2ddf84-b440-40c3-a549-1ab0edc462cc", "metadata": {}, "source": [ "### Timestamp methods and properties\n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 7, "id": "5a9d109a-0cc0-4471-9fb6-2173b463f1bd", "metadata": { "execution": { "iopub.execute_input": "2025-12-12T02:37:11.956569Z", "iopub.status.busy": "2025-12-12T02:37:11.956437Z", "iopub.status.idle": "2025-12-12T02:37:11.969824Z", "shell.execute_reply": "2025-12-12T02:37:11.969465Z" } }, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dtday_defaultday_eastern
2024-08-09 23:00:00-07:00910
2024-08-09 23:00:00-07:00910
2024-08-09 20:00:00-07:0099
" ], "text/plain": [ " dt day_default day_eastern\n", "0 2024-08-09 23:00:00-07:00 9 10\n", "1 2024-08-09 23:00:00-07:00 9 10\n", "2 2024-08-09 20:00:00-07:00 9 9" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "t.select(\n", " t.dt,\n", " day_default=t.dt.day,\n", " day_eastern=t.dt.astimezone('America/New_York').day,\n", ").collect()" ] }, { "cell_type": "markdown", "id": "af8c8776-5673-40ec-92c0-fb66c5750cb9", "metadata": {}, "source": [ "Observe that the first two timestamps map to different dates depending on the time zone, as expected." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.18" } }, "nbformat": 4, "nbformat_minor": 5 }