Search Shortcut cmd + k | ctrl + k

- Installation
- Documentation

- Getting Started
- Connect

- Overview
- Concurrency

- Data Import

- Overview
- Data Sources
- CSV Files

- Overview
- Auto Detection
- Reading Faulty CSV Files
- Tips

- JSON Files

- Overview
- Creating JSON
- Loading JSON
- Writing JSON
- JSON Type
- JSON Functions
- Format Settings
- Installing and Loading
- SQL to / from JSON
- Caveats

- Multiple Files

- Overview
- Combining Schemas

- Parquet Files

- Overview
- Metadata
- Encryption
- Tips

- Partitioning

- Hive Partitioning
- Partitioned Writes

- Appender
- INSERT Statements

- Client APIs

- Overview
- C

- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference

- C++
- CLI

- Overview
- Arguments
- Dot Commands
- Output Formats
- Editing
- Autocomplete
- Syntax Highlighting

- Dart
- Go
- Java
- Julia
- Node.js (Neo)

- Overview

- Node.js

- Overview
- API Reference

- Python

- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues

- R
- Rust
- Swift
- Wasm

- Overview
- Instantiation
- Data Ingestion
- Query
- Extensions

- ADBC
- ODBC

- Overview
- Linux Setup
- Windows Setup
- macOS Setup
- Configuration

- SQL

- Introduction
- Statements

- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH and DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DESCRIBE
- DROP
- EXPORT and IMPORT DATABASE
- INSERT
- LOAD / INSTALL
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM

- Query Syntax

- SELECT
- FROM and JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT and OFFSET
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements

- Data Types

- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting

- Expressions

- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries

- Functions

- Overview
- Aggregate Functions
- Array Functions
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- List Functions
- Map Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Struct Functions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Union Functions
- Utility Functions
- Window Functions

- Constraints
- Indexes
- Meta Queries

- Information Schema
- Metadata Functions

- DuckDB's SQL Dialect

- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility

- Samples

- Configuration

- Overview
- Pragmas
- Secrets Manager

- Extensions

- Overview
- Core Extensions
- Community Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Delta
- Excel
- Full Text Search
- httpfs (HTTP and S3)

- Overview
- HTTP(S) Support
- Hugging Face
- S3 API Support
- Legacy Authentication Scheme for S3 API

- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial

- Overview
- Function Reference
- R-Tree Indexes
- GDAL Integration

- SQLite
- Substrait
- TPC-DS
- TPC-H
- VSS

- Guides

- Overview
- Data Viewers

- Tableau
- CLI Charting with YouPlot

- Database Integration

- Overview
- MySQL Import
- PostgreSQL Import
- SQLite Import

- File Formats

- Overview
- CSV Import
- CSV Export
- Directly Reading Files
- Excel Import
- Excel Export
- JSON Import
- JSON Export
- Parquet Import
- Parquet Export
- Querying Parquet Files

- Network and Cloud Storage

- Overview
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- DuckDB over HTTPS / S3

- Meta Queries

- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment

- ODBC

- ODBC Guide

- Performance

- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks

- Python

- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems

- SQL Editors

- DBeaver SQL IDE

- SQL Features

- AsOf Join
- Full-Text Search
- query and query_table Functions

- Snippets

- Create Synthetic Data
- Sharing Macros

- Glossary of Terms
- Browse Offline

- Operations Manual

- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint

- Files Created by DuckDB
- Gitignore for DuckDB
- Reclaiming Space

- Securing DuckDB

- Overview
- Securing Extensions

- Development

- DuckDB Repositories
- Testing

- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch

- Profiling
- Release Calendar
- Building

- Overview
- Build Instructions
- Build Configuration
- Building Extensions
- Platforms
- Troubleshooting

- Benchmark Suite

- Internals

- Overview
- Storage Versions and Format
- Execution Format
- Pivot

- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo

Timestamp with Time Zone Functions

This section describes functions and operators for examining and manipulating `TIMESTAMP WITH TIME ZONE` (or `TIMESTAMPTZ`) values.

See also the related `TIMESTAMP` functions.

Despite the name, these values do not store a time zone – just an instant like `TIMESTAMP`. Instead, they request that the instant be binned and formatted using the current time zone, see the blog post on DuckDB's timezone support for more information.

Time zone support is provided by the built-in ICU extension.

In the examples below, the current time zone is presumed to be `America/Los_Angeles` using the Gregorian calendar.

## Built-In Timestamp with Time Zone Functions

The table below shows the available scalar functions for `TIMESTAMPTZ` values. Since these functions do not involve binning or display, they are always available.

Name | Description
--- | ---
`current_timestamp` | Current date and time (start of current transaction).
`get_current_timestamp()` | Current date and time (start of current transaction).
`greatest(timestamptz, timestamptz)` | The later of two timestamps.
`isfinite(timestamptz)` | Returns true if the timestamp with time zone is finite, false otherwise.
`isinf(timestamptz)` | Returns true if the timestamp with time zone is infinite, false otherwise.
`least(timestamptz, timestamptz)` | The earlier of two timestamps.
`now()` | Current date and time (start of current transaction).
`to_timestamp(double)` | Converts seconds since the epoch to a timestamp with time zone.
`transaction_timestamp()` | Current date and time (start of current transaction).

#### `current_timestamp`

Description | Current date and time (start of current transaction).
Example | `current_timestamp`
Result | `2022-10-08 12:44:46.122-07`

#### `get_current_timestamp()`

Description | Current date and time (start of current transaction).
Example | `get_current_timestamp()`
Result | `2022-10-08 12:44:46.122-07`

#### `greatest(timestamptz, timestamptz)`

Description | The later of two timestamps.
Example | `greatest(TIMESTAMPTZ '1992-09-20 20:38:48', TIMESTAMPTZ '1992-03-22 01:02:03.1234')`
Result | `1992-09-20 20:38:48-07`

#### `isfinite(timestamptz)`

Description | Returns true if the timestamp with time zone is finite, false otherwise.
Example | `isfinite(TIMESTAMPTZ '1992-03-07')`
Result | `true`

#### `isinf(timestamptz)`

Description | Returns true if the timestamp with time zone is infinite, false otherwise.
Example | `isinf(TIMESTAMPTZ '-infinity')`
Result | `true`

#### `least(timestamptz, timestamptz)`

Description | The earlier of two timestamps.
Example | `least(TIMESTAMPTZ '1992-09-20 20:38:48', TIMESTAMPTZ '1992-03-22 01:02:03.1234')`
Result | `1992-03-22 01:02:03.1234-08`

#### `now()`

Description | Current date and time (start of current transaction).
Example | `now()`
Result | `2022-10-08 12:44:46.122-07`

#### `to_timestamp(double)`

Description | Converts seconds since the epoch to a timestamp with time zone.
Example | `to_timestamp(1284352323.5)`
Result | `2010-09-13 04:32:03.5+00`

#### `transaction_timestamp()`

Description | Current date and time (start of current transaction).
Example | `transaction_timestamp()`
Result | `2022-10-08 12:44:46.122-07`

## Timestamp with Time Zone Strings

With no time zone extension loaded, `TIMESTAMPTZ` values will be cast to and from strings using offset notation. This will let you specify an instant correctly without access to time zone information. For portability, `TIMESTAMPTZ` values will always be displayed using GMT offsets:

    SELECT '2022-10-08 13:13:34-07'::TIMESTAMPTZ;

    2022-10-08 20:13:34+00

If a time zone extension such as ICU is loaded, then a time zone can be parsed from a string and cast to a representation in the local time zone:

    SELECT '2022-10-08 13:13:34 Europe/Amsterdam'::TIMESTAMPTZ::VARCHAR;

    2022-10-08 04:13:34-07 -- the offset will differ based on your local time zone

## ICU Timestamp with Time Zone Operators

The table below shows the available mathematical operators for `TIMESTAMP WITH TIME ZONE` values provided by the ICU extension.

Operator | Description | Example | Result
--- | --- | --- | ---
`+` | addition of an `INTERVAL` | `TIMESTAMPTZ '1992-03-22 01:02:03' + INTERVAL 5 DAY` | `1992-03-27 01:02:03`
`-` | subtraction of `TIMESTAMPTZ`s | `TIMESTAMPTZ '1992-03-27' - TIMESTAMPTZ '1992-03-22'` | `5 days`
`-` | subtraction of an `INTERVAL` | `TIMESTAMPTZ '1992-03-27 01:02:03' - INTERVAL 5 DAY` | `1992-03-22 01:02:03`

Adding to or subtracting from infinite values produces the same infinite value.

## ICU Timestamp with Time Zone Functions

The table below shows the ICU provided scalar functions for `TIMESTAMP WITH TIME ZONE` values.

Name | Description
--- | ---
`age(timestamptz, timestamptz)` | Subtract arguments, resulting in the time difference between the two timestamps.
`age(timestamptz)` | Subtract from current_date.
`date_diff(part, startdate, enddate)` | The number of partition boundaries between the timestamps.
`date_part([part, ...], timestamptz)` | Get the listed subfields as a `struct`. The list must be constant.
`date_part(part, timestamptz)` | Get subfield (equivalent to extract).
`date_sub(part, startdate, enddate)` | The number of complete partitions between the timestamps.
`date_trunc(part, timestamptz)` | Truncate to specified precision.
`datediff(part, startdate, enddate)` | Alias of date_diff. The number of partition boundaries between the timestamps.
`datepart([part, ...], timestamptz)` | Alias of date_part. Get the listed subfields as a `struct`. The list must be constant.
`datepart(part, timestamptz)` | Alias of date_part. Get subfield (equivalent to extract).
`datesub(part, startdate, enddate)` | Alias of date_sub. The number of complete partitions between the timestamps.
`datetrunc(part, timestamptz)` | Alias of date_trunc. Truncate to specified precision.
`epoch_ms(timestamptz)` | Converts a timestamptz to milliseconds since the epoch.
`epoch_ns(timestamptz)` | Converts a timestamptz to nanoseconds since the epoch.
`epoch_us(timestamptz)` | Converts a timestamptz to microseconds since the epoch.
`extract(field FROM timestamptz)` | Get subfield from a `TIMESTAMP WITH TIME ZONE`.
`last_day(timestamptz)` | The last day of the month.
`make_timestamptz(bigint, bigint, bigint, bigint, bigint, double, string)` | The `TIMESTAMP WITH TIME ZONE` for the given parts and time zone.
`make_timestamptz(bigint, bigint, bigint, bigint, bigint, double)` | The `TIMESTAMP WITH TIME ZONE` for the given parts in the current time zone.
`make_timestamptz(microseconds)` | The `TIMESTAMP WITH TIME ZONE` for the given µs since the epoch.
`strftime(timestamptz, format)` | Converts a `TIMESTAMP WITH TIME ZONE` value to string according to the format string.
`strptime(text, format)` | Converts string to `TIMESTAMP WITH TIME ZONE` according to the format string if `%Z` is specified.
`time_bucket(bucket_width, timestamptz[, offset])` | Truncate `timestamptz` by the specified interval `bucket_width`. Buckets are offset by `offset` interval.
`time_bucket(bucket_width, timestamptz[, origin])` | Truncate `timestamptz` by the specified interval `bucket_width`. Buckets are aligned relative to `origin` timestamptz. `origin` defaults to 2000-01-03 00:00:00+00 for buckets that don't include a month or year interval, and to 2000-01-01 00:00:00+00 for month and year buckets.
`time_bucket(bucket_width, timestamptz[, timezone])` | Truncate `timestamptz` by the specified interval `bucket_width`. Bucket starts and ends are calculated using `timezone`. `timezone` is a varchar and defaults to UTC.

#### `age(timestamptz, timestamptz)`

Description | Subtract arguments, resulting in the time difference between the two timestamps.
Example | `age(TIMESTAMPTZ '2001-04-10', TIMESTAMPTZ '1992-09-20')`
Result | `8 years 6 months 20 days`

#### `age(timestamptz)`

Description | Subtract from current_date.
Example | `age(TIMESTAMP '1992-09-20')`
Result | `29 years 1 month 27 days 12:39:00.844`

#### `date_diff(part, startdate, enddate)`

Description | The number of partition boundaries between the timestamps.
Example | `date_diff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')`
Result | `2`

#### `date_part([part, ...], timestamptz)`

Description | Get the listed subfields as a `struct`. The list must be constant.
Example | `date_part(['year', 'month', 'day'], TIMESTAMPTZ '1992-09-20 20:38:40-07')`
Result | `{year: 1992, month: 9, day: 20}`

#### `date_part(part, timestamptz)`

Description | Get subfield (equivalent to extract).
Example | `date_part('minute', TIMESTAMPTZ '1992-09-20 20:38:40')`
Result | `38`

#### `date_sub(part, startdate, enddate)`

Description | The number of complete partitions between the timestamps.
Example | `date_sub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')`
Result | `1`

#### `date_trunc(part, timestamptz)`

Description | Truncate to specified precision.
Example | `date_trunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40')`
Result | `1992-09-20 20:00:00`

#### `datediff(part, startdate, enddate)`

Description | Alias of date_diff. The number of partition boundaries between the timestamps.
Example | `datediff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')`
Result | `2`

#### `datepart([part, ...], timestamptz)`

Description | Alias of date_part. Get the listed subfields as a `struct`. The list must be constant.
Example | `datepart(['year', 'month', 'day'], TIMESTAMPTZ '1992-09-20 20:38:40-07')`
Result | `{year: 1992, month: 9, day: 20}`

#### `datepart(part, timestamptz)`

Description | Alias of date_part. Get subfield (equivalent to extract).
Example | `datepart('minute', TIMESTAMPTZ '1992-09-20 20:38:40')`
Result | `38`

#### `datesub(part, startdate, enddate)`

Description | Alias of date_sub. The number of complete partitions between the timestamps.
Example | `datesub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')`
Result | `1`

#### `datetrunc(part, timestamptz)`

Description | Alias of date_trunc. Truncate to specified precision.
Example | `datetrunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40')`
Result | `1992-09-20 20:00:00`

#### `epoch_ms(timestamptz)`

Description | Converts a timestamptz to milliseconds since the epoch.
Example | `epoch_ms('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ);`
Result | `1667810584123`

#### `epoch_ns(timestamptz)`

Description | Converts a timestamptz to nanoseconds since the epoch.
Example | `epoch_ns('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ);`
Result | `1667810584123456000`

#### `epoch_us(timestamptz)`

Description | Converts a timestamptz to microseconds since the epoch.
Example | `epoch_us('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ);`
Result | `1667810584123456`

#### `extract(field FROM timestamptz)`

Description | Get subfield from a `TIMESTAMP WITH TIME ZONE`.
Example | `extract('hour' FROM TIMESTAMPTZ '1992-09-20 20:38:48')`
Result | `20`

#### `last_day(timestamptz)`

Description | The last day of the month.
Example | `last_day(TIMESTAMPTZ '1992-03-22 01:02:03.1234')`
Result | `1992-03-31`

#### `make_timestamptz(bigint, bigint, bigint, bigint, bigint, double, string)`

Description | The `TIMESTAMP WITH TIME ZONE` for the given parts and time zone.
Example | `make_timestamptz(1992, 9, 20, 15, 34, 27.123456, 'CET')`
Result | `1992-09-20 06:34:27.123456-07`

#### `make_timestamptz(bigint, bigint, bigint, bigint, bigint, double)`

Description | The `TIMESTAMP WITH TIME ZONE` for the given parts in the current time zone.
Example | `make_timestamptz(1992, 9, 20, 13, 34, 27.123456)`
Result | `1992-09-20 13:34:27.123456-07`

#### `make_timestamptz(microseconds)`

Description | The `TIMESTAMP WITH TIME ZONE` for the given µs since the epoch.
Example | `make_timestamptz(1667810584123456)`
Result | `2022-11-07 16:43:04.123456-08`

#### `strftime(timestamptz, format)`

Description | Converts a `TIMESTAMP WITH TIME ZONE` value to string according to the format string.
Example | `strftime(timestamptz '1992-01-01 20:38:40', '%a, %-d %B %Y - %I:%M:%S %p')`
Result | `Wed, 1 January 1992 - 08:38:40 PM`

#### `strptime(text, format)`

Description | Converts string to `TIMESTAMP WITH TIME ZONE` according to the format string if `%Z` is specified.
Example | `strptime('Wed, 1 January 1992 - 08:38:40 PST', '%a, %-d %B %Y - %H:%M:%S %Z')`
Result | `1992-01-01 08:38:40-08`

#### `time_bucket(bucket_width, timestamptz[, offset])`

Description | Truncate `timestamptz` by the specified interval `bucket_width`. Buckets are offset by `offset` interval.
Example | `time_bucket(INTERVAL '10 minutes', TIMESTAMPTZ '1992-04-20 15:26:00-07', INTERVAL '5 minutes')`
Result | `1992-04-20 15:25:00-07`

#### `time_bucket(bucket_width, timestamptz[, origin])`

Description | Truncate `timestamptz` by the specified interval `bucket_width`. Buckets are aligned relative to `origin` timestamptz. `origin` defaults to 2000-01-03 00:00:00+00 for buckets that don't include a month or year interval, and to 2000-01-01 00:00:00+00 for month and year buckets.
Example | `time_bucket(INTERVAL '2 weeks', TIMESTAMPTZ '1992-04-20 15:26:00-07', TIMESTAMPTZ '1992-04-01 00:00:00-07')`
Result | `1992-04-15 00:00:00-07`

#### `time_bucket(bucket_width, timestamptz[, timezone])`

Description | Truncate `timestamptz` by the specified interval `bucket_width`. Bucket starts and ends are calculated using `timezone`. `timezone` is a varchar and defaults to UTC.
Example | `time_bucket(INTERVAL '2 days', TIMESTAMPTZ '1992-04-20 15:26:00-07', 'Europe/Berlin')`
Result | `1992-04-19 15:00:00-07`

There are also dedicated extraction functions to get the subfields.

## ICU Timestamp Table Functions

The table below shows the available table functions for `TIMESTAMP WITH TIME ZONE` types.

Name | Description
--- | ---
`generate_series(timestamptz, timestamptz, interval)` | Generate a table of timestamps in the closed range (including both the starting timestamp and the ending timestamp), stepping by the interval.
`range(timestamptz, timestamptz, interval)` | Generate a table of timestamps in the half open range (including the starting timestamp, but stopping before the ending timestamp), stepping by the interval.

> Infinite values are not allowed as table function bounds.

#### `generate_series(timestamptz, timestamptz, interval)`

Description | Generate a table of timestamps in the closed range (including both the starting timestamp and the ending timestamp), stepping by the interval.
Example | `generate_series(TIMESTAMPTZ '2001-04-10', TIMESTAMPTZ '2001-04-11', INTERVAL 30 MINUTE)`

#### `range(timestamptz, timestamptz, interval)`

Description | Generate a table of timestamps in the half open range (including the starting timestamp, but stopping before the ending timestamp), stepping by the interval.
Example | `range(TIMESTAMPTZ '2001-04-10', TIMESTAMPTZ '2001-04-11', INTERVAL 30 MINUTE)`

## ICU Timestamp Without Time Zone Functions

The table below shows the ICU provided scalar functions that operate on plain `TIMESTAMP` values. These functions assume that the `TIMESTAMP` is a “local timestamp”.

A local timestamp is effectively a way of encoding the part values from a time zone into a single value. They should be used with caution because the produced values can contain gaps and ambiguities thanks to daylight savings time. Often the same functionality can be implemented more reliably using the `struct` variant of the `date_part` function.

Name | Description
--- | ---
`current_localtime()` | Returns a `TIME` whose GMT bin values correspond to local time in the current time zone.
`current_localtimestamp()` | Returns a `TIMESTAMP` whose GMT bin values correspond to local date and time in the current time zone.
`localtime` | Synonym for the `current_localtime()` function call.
`localtimestamp` | Synonym for the `current_localtimestamp()` function call.
`timezone(text, timestamp)` | Use the date parts of the timestamp in GMT to construct a timestamp in the given time zone. Effectively, the argument is a “local” time.
`timezone(text, timestamptz)` | Use the date parts of the timestamp in the given time zone to construct a timestamp. Effectively, the result is a “local” time.

#### `current_localtime()`

Description | Returns a `TIME` whose GMT bin values correspond to local time in the current time zone.
Example | `current_localtime()`
Result | `08:47:56.497`

#### `current_localtimestamp()`

Description | Returns a `TIMESTAMP` whose GMT bin values correspond to local date and time in the current time zone.
Example | `current_localtimestamp()`
Result | `2022-12-17 08:47:56.497`

#### `localtime`

Description | Synonym for the `current_localtime()` function call.
Example | `localtime`
Result | `08:47:56.497`

#### `localtimestamp`

Description | Synonym for the `current_localtimestamp()` function call.
Example | `localtimestamp`
Result | `2022-12-17 08:47:56.497`

#### `timezone(text, timestamp)`

Description | Use the date parts of the timestamp in GMT to construct a timestamp in the given time zone. Effectively, the argument is a “local” time.
Example | `timezone('America/Denver', TIMESTAMP '2001-02-16 20:38:40')`
Result | `2001-02-16 19:38:40-08`

#### `timezone(text, timestamptz)`

Description | Use the date parts of the timestamp in the given time zone to construct a timestamp. Effectively, the result is a “local” time.
Example | `timezone('America/Denver', TIMESTAMPTZ '2001-02-16 20:38:40-05')`
Result | `2001-02-16 18:38:40`

## At Time Zone

The `AT TIME ZONE` syntax is syntactic sugar for the (two argument) `timezone` function listed above:

    SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver' AS ts;

    2001-02-16 19:38:40-08

    SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver' AS ts;

    2001-02-16 18:38:40

Note that numeric timezones are not allowed:

    SELECT TIMESTAMP '2001-02-16 20:38:40-05' AT TIME ZONE '0200' AS ts;

    Not implemented Error: Unknown TimeZone '0200'

## Infinities

Functions applied to infinite dates will either return the same infinite dates (e.g, `greatest`) or `NULL` (e.g., `date_part`) depending on what “makes sense”. In general, if the function needs to examine the parts of the infinite temporal value, the result will be `NULL`.

## Calendars

The ICU extension also supports non-Gregorian calendars. If such a calendar is current, then the display and binning operations will use that calendar.

##### About this page

- Report content issue
- Edit this page on GitHub