Join us on YugabyteDB Community Slack
Star us on
Get Started
Slack
GitHub
Get Started
v2.13 (preview) v2.12 (stable) v2.8 (earlier version) v2.6 (earlier version) v2.4 (earlier version) Unsupported versions
  • YUGABYTEDB CORE
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore distributed SQL
      • 4. Build an application
        • Java
        • Node.js
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
        • Scala
        • Rust
    • Explore
      • SQL features
        • SQL Feature Support
        • PostgreSQL Compatibility
        • Foreign Data Wrappers
        • Schemas and Tables
        • Data Types
        • Data Manipulation
        • Queries and Joins
        • Expressions and Operators
        • Stored Procedures
        • Triggers
        • Advanced features
          • Cursors
          • Table Partitioning
          • Views
          • Savepoints
          • Collations
        • Going beyond SQL
          • Follower reads
          • Tablespaces
        • PostgreSQL extensions
      • Fault tolerance
      • Horizontal scalability
        • Scaling Transactions
        • Sharding Data
      • Transactions
        • Distributed Transactions
        • Isolation Levels
        • Explicit Locking
      • Indexes and Constraints
        • Primary keys
        • Foreign keys
        • Secondary indexes
        • Unique indexes
        • Partial indexes
        • Expression indexes
        • Covering indexes
        • GIN indexes
        • Other constraints
      • JSON support
      • Multi-region deployments
        • Sync replication (3+ regions)
        • Async Replication (2+ regions)
        • Row-Level Geo-Partitioning
        • Read replicas
      • Query tuning
        • Introduction
        • Get query statistics using pg_stat_statements
        • Viewing live queries with pg_stat_activity
        • Analyzing queries with EXPLAIN
        • Optimizing YSQL queries using pg_hint_plan
      • Cluster management
        • Point-in-time recovery
      • Change data capture (CDC)
        • Debezium connector
        • Java CDC console
      • Security
      • Observability
        • Prometheus Integration
        • Grafana Dashboard
    • Drivers and ORMs
      • Java
        • JDBC drivers
        • Hibernate ORM
        • Supported versions
      • Go
        • Go drivers
        • Go ORMs
        • Supported versions
      • C#
        • C# drivers
        • C# ORMs
        • Supported versions
      • NodeJS
        • NodeJS drivers
        • NodeJS ORMs
        • Supported Versions
      • Python
        • Python drivers
        • Python ORMs
        • Supported versions
      • Rust
        • Diesel ORM
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
        • 9. TTL for data expiration
      • Real-world examples
        • E-Commerce app
        • IoT fleet management
      • Explore sample apps
      • Best practices
      • Cloud-native development
        • Codespaces
        • Gitpod
    • Migrate
      • Migration process overview
      • Migrate from PostgreSQL
        • Convert a PostgreSQL schema
        • Migrate a PostgreSQL application
        • Export PostgreSQL data
        • Prepare a cluster
        • Import PostgreSQL data
        • Verify Migration
    • Deploy
      • Deployment checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Single-zone
          • Open Source
          • Amazon EKS
          • Google Kubernetes Engine
          • Azure Kubernetes Service
        • Multi-zone
          • Amazon EKS
          • Google Kubernetes Engine
        • Multi-cluster
          • Google Kubernetes Engine
        • Best practices
        • Connect Clients
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Multi-DC deployments
        • Three+ data center (3DC)
        • Asynchronous Replication
        • Read replica clusters
    • Benchmark
      • TPC-C
      • sysbench
      • YCSB
      • Key-value workload
      • Large datasets
      • Scalability
        • Scaling queries
      • Resilience
        • Jepsen testing
      • Performance Troubleshooting
    • Secure
      • Security checklist
      • Enable authentication
        • Enable users
        • Configure client authentication
      • Authentication methods
        • Password authentication
        • LDAP authentication
        • Host-based authentication
        • Trust authentication
      • Role-based access control
        • Overview
        • Manage users and roles
        • Grant privileges
        • Row-level security
        • Column-level security
      • Encryption in transit
        • Create server certificates
        • Enable server-to-server encryption
        • Enable client-to-server encryption
        • Connect to clusters
        • TLS and authentication
      • Encryption at rest
      • Column-level encryption
      • Audit logging
        • Configure audit logging
        • Session-Level Audit Logging
        • Object-Level Audit Logging
      • Vulnerability disclosure policy
    • Manage
      • Back up and restore
        • Export and import data
        • Snapshot and restore data
        • Point-in-time recovery
      • Migrate data
        • Bulk import
        • Bulk export
      • Change cluster configuration
      • Diagnostics reporting
      • Upgrade a deployment
      • Grow cluster
    • Troubleshoot
      • Troubleshooting
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
        • Recover tserver/master
        • Replace a failed YB-TServer
        • Replace a failed YB-Master
        • Manual remote bootstrap when a majority of peers fail
      • Node level issues
        • Check servers
        • Inspect logs
        • System statistics
        • Disk failure
        • Common error messages
    • Contribute
      • Core database
        • Contribution checklist
        • Build the source
        • Configure a CLion project
        • Run the tests
        • Coding style
      • Documentation
        • Docs checklist
        • Docs layout
        • Build the docs
          • Editor setup
        • Edit the docs
          • Docs page structure
          • Syntax diagrams
        • Style guide
  • YUGABYTEDB ANYWHERE
    • Overview
      • Install
      • Configure
    • Install
      • Prerequisites
      • Prepare the environment
      • Install software
      • Prepare nodes
      • Uninstall software
    • Configure
      • Create admin user
      • Configure cloud providers
      • Configure backup target
      • Configure alerts
    • Create deployments
      • Multi-zone universe
      • Multi-region universe
      • Multi-cloud universe
      • Read replica cluster
      • Asynchronous replication
    • Manage deployments
      • Start and stop processes
      • Eliminate an unresponsive node
      • Recover a node
      • Enable high availability
      • Edit configuration flags
      • Edit a universe
      • Delete a universe
      • Configure instance tags
      • Upgrade YugabyteDB
      • Migrate to Helm 3
    • Back up universes
      • Configure backup storage
      • Back up universe data
      • Restore universe data
      • Schedule data backups
    • Security
      • Security checklist
      • Configure ports
      • LDAP authentication
      • Authorization
      • Create a KMS configuration
      • Enable encryption at rest
      • Enable encryption in transit
      • Network security
    • Alerts and monitoring
      • Alerts
      • Live Queries dashboard
      • Slow Queries dashboard
    • Troubleshoot
      • Install and upgrade issues
      • Universe issues
    • Administer
      • Back up YugabyteDB Anywhere
      • Authenticate with LDAP
    • Upgrade
      • Upgrade Kubernetes installation
      • Upgrade using Replicated
  • YUGABYTEDB MANAGED
    • Overview
    • Quick start
      • Create a free cluster
      • Connect to the cluster
      • Explore distributed SQL
      • Build an application
        • Before you begin
        • Java
        • Go
        • Python
        • Node.js
        • C
        • C++
        • C#
        • Ruby
        • Rust
        • PHP
    • Deploy clusters
      • Planning a cluster
      • Create a free cluster
      • Create a standard cluster
      • VPC network
        • Overview
        • VPCs
        • Peering connections
        • Create a VPC Network
    • Secure clusters
      • IP allow lists
      • Database authorization
      • Add database users
      • Encryption in transit
      • Audit account activity
    • Connect to clusters
      • Cloud Shell
      • Client shell
      • Connect applications
    • Alerts and monitoring
      • Alerts
      • Performance metrics
      • Live queries
      • Slow YSQL queries
      • Cluster activity
    • Manage clusters
      • Scale and configure clusters
      • Backup and restore
      • Maintenance windows
      • Create extensions
    • Administration and billing
      • Manage account access
      • Manage billing
      • Cluster costs
    • Example applications
      • Connect a Spring application
      • Connect a YCQL Java application
      • Hasura Cloud
      • Deploy a GraphQL application
    • Security architecture
      • Security architecture
      • Shared responsibility model
    • Troubleshoot
    • YugabyteDB Managed FAQ
    • What's new
  • INTEGRATIONS
    • Apache Kafka
    • Apache Spark
    • Debezium
    • Django REST framework
    • Entity Framework
    • Flyway
    • GORM
    • Hasura
      • Application Development
      • Benchmarking
    • JanusGraph
    • KairosDB
    • Liquibase
    • Metabase
    • Presto
    • Prisma
    • Sequelize
    • Spring Framework
      • Spring Data YugabyteDB
      • Spring Data JPA
      • Spring Data Cassandra
    • SQLAlchemy
    • WSO2 Identity Server
    • YSQL Loader
    • YugabyteDB JDBC driver
  • REFERENCE
    • Architecture
      • Design goals
      • Key concepts
        • Universe
        • YB-TServer Service
        • YB-Master Service
      • Core functions
        • Universe creation
        • Table creation
        • Write IO path
        • Read IO path
        • High availability
      • Layered architecture
      • Query layer
        • Overview
      • DocDB transactions layer
        • Transactions overview
        • Transaction isolation levels
        • Explicit locking
        • Read Committed
        • Single-row transactions
        • Distributed transactions
        • Transactional IO path
      • DocDB sharding layer
        • Hash & range sharding
        • Tablet splitting
        • Colocated tables
      • DocDB replication layer
        • Replication
        • xCluster replication
        • Read replicas
        • Change data capture (CDC)
      • DocDB storage layer
        • Persistence
        • Performance
    • APIs
      • YSQL
        • The SQL language
          • SQL statements
            • ABORT
            • ALTER DATABASE
            • ALTER DEFAULT PRIVILEGES
            • ALTER DOMAIN
            • ALTER FOREIGN DATA WRAPPER
            • ALTER FOREIGN TABLE
            • ALTER GROUP
            • ALTER POLICY
            • ALTER ROLE
            • ALTER SEQUENCE
            • ALTER SERVER
            • ALTER TABLE
            • ALTER USER
            • ANALYZE
            • BEGIN
            • CALL
            • COMMENT
            • COMMIT
            • COPY
            • CREATE AGGREGATE
            • CREATE CAST
            • CREATE DATABASE
            • CREATE DOMAIN
            • CREATE EXTENSION
            • CREATE FOREIGN DATA WRAPPER
            • CREATE FOREIGN TABLE
            • CREATE FUNCTION
            • CREATE GROUP
            • CREATE INDEX
            • CREATE MATERIALIZED VIEW
            • CREATE OPERATOR
            • CREATE OPERATOR CLASS
            • CREATE POLICY
            • CREATE PROCEDURE
            • CREATE ROLE
            • CREATE RULE
            • CREATE SCHEMA
            • CREATE SEQUENCE
            • CREATE SERVER
            • CREATE TABLE
            • CREATE TABLE AS
            • CREATE TRIGGER
            • CREATE TYPE
            • CREATE USER
            • CREATE USER MAPPING
            • CREATE VIEW
            • DEALLOCATE
            • DELETE
            • DO
            • DROP AGGREGATE
            • DROP CAST
            • DROP DATABASE
            • DROP DOMAIN
            • DROP EXTENSION
            • DROP FOREIGN DATA WRAPPER
            • DROP FOREIGN TABLE
            • DROP FUNCTION
            • DROP GROUP
            • DROP MATERIALIZED VIEW
            • DROP OPERATOR
            • DROP OPERATOR CLASS
            • DROP OWNED
            • DROP POLICY
            • DROP PROCEDURE
            • DROP ROLE
            • DROP RULE
            • DROP SEQUENCE
            • DROP SERVER
            • DROP TABLE
            • DROP TRIGGER
            • DROP TYPE
            • DROP USER
            • END
            • EXECUTE
            • EXPLAIN
            • GRANT
            • IMPORT FOREIGN SCHEMA
            • INSERT
            • LOCK
            • PREPARE
            • REASSIGN OWNED
            • REFRESH MATERIALIZED VIEW
            • RELEASE SAVEPOINT
            • RESET
            • REVOKE
            • ROLLBACK
            • ROLLBACK TO SAVEPOINT
            • SAVEPOINT
            • SELECT
            • SET
            • SET CONSTRAINTS
            • SET ROLE
            • SET SESSION AUTHORIZATION
            • SET TRANSACTION
            • SHOW
            • SHOW TRANSACTION
            • TRUNCATE
            • UPDATE
            • VALUES
          • WITH clause
            • WITH clause—SQL syntax and semantics
            • recursive CTE
            • case study—traversing an employee hierarchy
            • traversing general graphs
              • graph representation
              • common code
              • undirected cyclic graph
              • directed cyclic graph
              • directed acyclic graph
              • rooted tree
              • Unique containing paths
              • Stress testing find_paths()
            • case study—Bacon Numbers from IMDb
              • Bacon numbers for synthetic data
              • Bacon numbers for IMDb data
        • Data types
          • Array
            • array[] constructor
            • Literals
              • Text typecasting and literals
              • Array of primitive values
              • Row
              • Array of rows
            • FOREACH loop (PL/pgSQL)
            • array of DOMAINs
            • Functions and operators
              • ANY and ALL
              • Array comparison
              • Array slice operator
              • Array concatenation
              • Array properties
              • array_agg(), unnest(), generate_subscripts()
              • array_fill()
              • array_position(), array_positions()
              • array_remove()
              • array_replace() / set value
              • array_to_string()
              • string_to_array()
          • Binary
          • Boolean
          • Character
          • Date and time
            • Conceptual background
            • Timezones and UTC offsets
              • Catalog views
              • Extended_timezone_names
                • Unrestricted full projection
                • Real timezones with DST
                • Real timezones no DST
                • Synthetic timezones no DST
              • Offset/timezone-sensitive operations
                • Timestamptz to/from timestamp conversion
                • Pure 'day' interval arithmetic
              • Four ways to specify offset
                • Name-resolution rules
                  • 1 case-insensitive resolution
                  • 2 ~names.abbrev never searched
                  • 3 'set timezone' string not resolved in ~abbrevs.abbrev
                  • 4 ~abbrevs.abbrev before ~names.name
                  • Helper functions
              • Syntax contexts for offset
              • Recommended practice
            • Typecasting between date-time and text-values
            • Semantics of the date-time data types
              • Date data type
              • Time data type
              • Plain timestamp and timestamptz
              • Interval data type
                • Interval representation
                  • Ad hoc examples
                  • Representation model
                • Interval value limits
                • Declaring intervals
                • Justify() and extract(epoch...)
                • Interval arithmetic
                  • Interval-interval comparison
                  • Interval-interval addition and subtraction
                  • Interval-number multiplication
                  • Moment-moment overloads of "-"
                  • Moment-interval overloads of "+" and "-"
                • Custom interval domains
                • Interval utility functions
            • Typecasting between date-time datatypes
            • Operators
              • Test comparison overloads
              • Test addition overloads
              • Test subtraction overloads
              • Test multiplication overloads
              • Test division overloads
            • General-purpose functions
              • Creating date-time values
              • Manipulating date-time values
              • Current date-time moment
              • Delaying execution
              • Miscellaneous
                • Function age()
                • Function extract() | date_part()
                • Implementations that model the overlaps operator
            • Formatting functions
            • Case study—SQL stopwatch
            • Download & install the date-time utilities
            • ToC
          • JSON
            • JSON literals
            • Primitive and compound data types
            • Code example conventions
            • Indexes and check constraints
            • Functions & operators
              • ::jsonb, ::json, ::text (typecast)
              • ->, ->>, #>, #>> (JSON subvalues)
              • - and #- (remove)
              • || (concatenation)
              • = (equality)
              • @> and <@ (containment)
              • ? and ?| and ?& (key or value existence)
              • array_to_json()
              • jsonb_agg()
              • jsonb_array_elements()
              • jsonb_array_elements_text()
              • jsonb_array_length()
              • jsonb_build_object()
              • jsonb_build_array()
              • jsonb_each()
              • jsonb_each_text()
              • jsonb_extract_path()
              • jsonb_extract_path_text() and json_extract_path_text()
              • jsonb_object()
              • jsonb_object_agg()
              • jsonb_object_keys()
              • jsonb_populate_record()
              • jsonb_populate_recordset()
              • jsonb_pretty()
              • jsonb_set() and jsonb_insert()
              • jsonb_strip_nulls()
              • jsonb_to_record()
              • jsonb_to_recordset()
              • jsonb_typeof()
              • row_to_json()
              • to_jsonb()
          • Money
          • Numeric
          • Range
          • Serial
          • UUID
        • Functions and operators
          • Aggregate functions
            • Informal functionality overview
            • Invocation syntax and semantics
            • grouping sets, rollup, cube
            • Per function signature and purpose
              • avg(), count(), max(), min(), sum()
              • array_agg(), string_agg(), jsonb_agg(), jsonb_object_agg()
              • bit_and(), bit_or(), bool_and(), bool_or()
              • variance(), var_pop(), var_samp(), stddev(), stddev_pop(), stddev_samp()
              • linear regression
                • covar_pop(), covar_samp(), corr()
                • regr_%()
              • mode(), percentile_disc(), percentile_cont()
              • rank(), dense_rank(), percent_rank(), cume_dist()
            • case study—percentile_cont() and the "68–95–99.7" rule
            • case study—linear regression on COVID data
              • Download the COVIDcast data
              • Ingest the COVIDcast data
                • Inspect the COVIDcast data
                • Copy the .csv files to staging tables
                • Check staged data conforms to the rules
                • Join the staged data into a single table
                • SQL scripts
                  • Create cr_staging_tables()
                  • Create cr_copy_from_scripts()
                  • Create assert_assumptions_ok()
                  • Create xform_to_covidcast_fb_survey_results()
                  • ingest-the-data.sql
              • Analyze the COVIDcast data
                • symptoms vs mask-wearing by day
                • Data for scatter-plot for 21-Oct-2020
                • Scatter-plot for 21-Oct-2020
                • SQL scripts
                  • analysis-queries.sql
                  • synthetic-data.sql
          • currval()
          • lastval()
          • nextval()
          • Window functions
            • Informal functionality overview
            • Invocation syntax and semantics
            • Per function signature and purpose
              • row_number(), rank() and dense_rank()
              • percent_rank(), cume_dist() and ntile()
              • first_value(), nth_value(), last_value()
              • lag(), lead()
              • Tables for the code examples
                • table t1
                • table t2
                • table t3
                • table t4
            • case study—analyzing a normal distribution
              • Bucket allocation scheme
              • do_clean_start.sql
              • cr_show_t4.sql
              • cr_dp_views.sql
              • cr_int_views.sql
              • cr_pr_cd_equality_report.sql
              • cr_bucket_using_width_bucket.sql
              • cr_bucket_dedicated_code.sql
              • do_assert_bucket_ok
              • cr_histogram.sql
              • cr_do_ntile.sql
              • cr_do_percent_rank.sql
              • cr_do_cume_dist.sql
              • do_populate_results.sql
              • do_report_results.sql
              • do_compare_dp_results.sql
              • do_demo.sql
              • Reports
                • Histogram report
                • dp-results
                • compare-dp-results
                • int-results
          • yb_hash_code()
        • Keywords
        • Reserved names
      • YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • EXPLAIN
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple expressions
        • Subscripted expressions
        • Function call
        • Operators
        • BLOB
        • BOOLEAN
        • Collection
        • FROZEN
        • INET
        • Integer and counter
        • Non-integer
        • TEXT
        • DATE, TIME, and TIMESTAMP
        • UUID and TIMEUUID
        • JSONB
        • Date and time
        • BATCH
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • ysqlsh
      • ycqlsh
      • yb-admin
      • yb-ts-cli
      • ysql_dump
      • ysql_dumpall
    • Configuration
      • yb-tserver
      • yb-master
      • yugabyted
      • Default ports
    • Drivers and ORMs
      • JDBC Drivers
      • C# Drivers
      • Go Drivers
      • Python Drivers
      • Client drivers for YSQL
      • Client drivers for YCQL
    • Connectors
      • Kafka Connect YugabyteDB
    • Third party tools
      • pgAdmin
      • Apache Superset
      • Arctype
      • DBeaver
      • TablePlus
      • DbSchema
      • SQL Workbench/J
      • Cassandra Workbench
    • Sample datasets
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
      • Retail Analytics
  • RELEASES
    • Releases overview
      • v2.13 series (preview)
      • v2.12 series (stable)
      • v2.11 series
      • v2.9 series
      • v2.8 series
      • v2.7 series
      • v2.6 series
      • v2.5 series
      • v2.4 series
      • v2.3 series
      • v2.2 series
      • v2.1 series
      • v2.0 series
      • v1.3 series
      • v1.2 series
    • Release versioning
  • FAQ
    • Comparisons
      • Amazon Aurora
      • Google Cloud Spanner
      • CockroachDB
      • TiDB
      • Vitess
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • PostgreSQL
      • Redis in-memory store
      • Apache HBase
    • General FAQ
    • Operations FAQ
    • API compatibility FAQ
    • YugabyteDB Anywhere FAQ
  • MISC
    • YEDIS
      • Quick start
      • Develop
        • Build an application
        • C#
        • C++
        • Go
        • Java
        • NodeJS
        • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
    • Legal
      • Third party software
> APIs > YSQL > Data types > Date and time > Timezones and UTC offsets > Offset/timezone-sensitive operations >

Sensitivity of converting between timestamptz and plain timestamp to the UTC offset

Report a doc issue Suggest new content Contributor guide
  • The philosophy of the demonstration's design
  • The demonstration
  • Interpretation and statement of the rules
    • The results at "Displaying all results using UTC"
    • The results at "Displaying each set of results using the timezone in which they were computed"

The semantic rules for the conversion, in each direction, rest on a common-sense convention. Each conversion uses a value for the UTC offset. And this value is always known: either because the at time zone operator specifies it (either explicitly or implicitly via a timezone name); or from the session's current TimeZone setting. This is explained in the section Four ways to specify the UTC offset.

  • When a timestamptz value is converted to a plain timestamp value, the target is assigned to the date-and-time-of-day component of the source value, when this is expressed with respect to the inevitably known reigning UTC offset.
  • When a plain timestamp value is converted to a timestamptz value, the target is assigned by normalizing to UTC according to the inevitably known reigning UTC offset.

The rules that this section explains underly the rules for the text to timestamptz conversion.

The philosophy of the demonstration's design

The demonstration uses the table function plain_timestamp_to_from_timestamp_tz(). The overall shape of this is very similar to that of these two functions:

  • The table function interval_arithmetic_results() presented in the "sensitivity of timestamptz-interval arithmetic to the current timezone" section. That section is the present section's peer under the parent section "Scenarios that are sensitive to the UTC offset and possibly, additionally, to the timezone".
  • The table function timestamptz_vs_plain_timestamp() presented in the "plain timestamp and timestamptz data types" section.

The demonstration sets these three goals:

  • Goal one: to use assert statements to test the identity of effect of the typecast operator and the at time zone current('TimeZone') operator in the two directions of interest: the plain timestamp to timestamptz direction and the timestamptz to plain timestamp direction:

  • Goal two: to use assert statements to test the assumed rules for the conversions in each direction, as empirical observations suggest they might be..

  • Goal three: to let you vizualize the rules for the conversions by outputting the "from-to" value pairs for each conversion direction on a single line, doing the conversions at each of a set of representative timezones, each of which has a different UTC offset. (The visualization is enriched by showing the conversion outcomes first with UTC as the session's current TimeZone setting and then with, for each outcome, the timezone at which the conversions are done as the session's current TimeZone setting.

The demonstration that follows is designed like this:

  • A table function, plain_timestamp_to_from_timestamp_tz(at_utc in boolean), is used to enable a convenient "running commentary" visualization. It has one formal parameter: a boolean to let you specify the visualization mode.

    • One choice for at_utc asks to see the plain timestamp and timestamptz values for each result row with the session's TimeZone set to UTC. (Of course, only the timestamptz values are affected by the setting.)
    • The other choice asks to see the values for each result row with the session's TimeZone set to what was reigning when the plain timestamp to timestamptz and the timestamptz to plain timestamp conversions were done for that row.
  • Two constants, one with data type plain timestamp and one with data type timestamptz are initialized so that the internal representations (as opposed to the metadata) are identical. Look:

    ts_plain    constant timestamp   not null := make_timestamp  (yyyy, mm, dd, hh, mi, ss);
    ts_with_tz  constant timestamptz not null := make_timestamptz(yyyy, mm, dd, hh, mi, ss, 'UTC');
    
  • Each uses the same constant int values, yyyy, mm, dd, hh, mi, and ss, to define the identical date-and-time part for each of the two moments. The fact that UTC is used for the timezone argument of the make_timestamptz() invocation ensures the required identity of the internal representations of the two moments—actually, both as plain timestamp values.

  • The extract(epoch from ... ) function is used to get the numbers of seconds, as constant double precision values, from the start of the epoch for the two moment values. These two numbers of seconds are actually identical. But two distinct names (ts_plain_epoch and ts_with_tz_epoch) are used for these to help the reader see the symmetry of the two tests—one in each direction.

  • A constant array, timezones, is initialized thus:

    timezones constant text[] not null := array[
                                                  'Pacific/Pago_Pago',
                                                  'America/Porto_Velho',
                                                  'Atlantic/South_Georgia',
                                                  'UTC',
                                                  'Africa/Tripoli',
                                                  'Asia/Dubai',
                                                  'Pacific/Kiritimati'
                                                ];
    
  • A foreach loop is run thus:

    foreach z in array timezones loop
    
  • At each loop iteration:

    • The session's TimeZone setting is set to the value that the iterand, z, specifies.

    • These assignments are made:

      ts_with_tz_1       := ts_plain::timestamptz;
      ts_with_tz_2       := ts_plain at time zone current_setting('timezone');
      ts_with_tz_1_epoch := extract(epoch from ts_with_tz_1);
      
      ts_plain_1         := ts_with_tz::timestamp;
      ts_plain_2         := ts_with_tz at time zone current_setting('timezone');
      ts_plain_1_epoch   := extract(epoch from ts_plain_1);
      
      z_epoch            := extract(epoch from utc_offset(z));
      
    • These assert statements are executed to show that the ::timestamp and ::timestamptz typecasts are identical to at time zone current_setting('timezone'):

      assert (ts_with_tz_2 = ts_with_tz_1), 'Assert #1 failed.';
      assert (ts_plain_2   = ts_plain_1  ), 'Assert #2 failed.';
      
    • These assert statements are executed to show that the expected rules for the conversion of the internal representations hold, in both directions between plain timestamp and timestamptz:

    assert ( ts_with_tz_1_epoch = (ts_plain_epoch   - z_epoch) ), 'Assert #3 failed.';
    assert ( ts_plain_1_epoch   = (ts_with_tz_epoch + z_epoch) ), 'Assert #4 failed.';
    
    • According to the choice for at_utc: either the timezone is set to UTC; or it is simply left at what the loop iterand, z, set it to. Then these values are formatted as a text line using the to_char() built-in function: ts_plain, ts_with_tz_1, ts_with_tz, and ts_plain_1. The row is labeled with the value of the loop iterand, z.
  • Finally, after the loop completes and before exiting, the session's TimeZone setting is restored to the value that it had on entry to the function. (It's always good practice to do this for any settings that your programs need, temporarily, to change.)

The demonstration

First, create a trivial function to return the pg_timezone_names.utc_offset value for a specified value of pg_timezonenames.name:

drop function if exists utc_offset(text) cascade;
create function utc_offset(tz_name in text)
  returns interval
  language plpgsql
as $body$
declare
  i constant interval not null := (
                                    select a.utc_offset from
                                    pg_timezone_names a
                                    where a.name = tz_name
                                  );
begin
  return i;
end;
$body$;

Now create a wrapper to return the utc_offset()'s returned interval value as text in exactly the format that the literal for a timestamptz value uses.

drop function if exists utc_offset_display(text) cascade;
create function utc_offset_display(tz_name in text)
  returns text
  language plpgsql
as $body$
declare
  i constant interval not null := utc_offset(tz_name);
  hh constant int     not null := extract(hours   from i);
  mm constant int     not null := extract(minutes from i);
  t  constant text    not null := to_char(hh, 's00')||
                                    case
                                      when mm <> 0 then ':'||ltrim(to_char(mm, '00'))
                                      else               ''
                                    end;
begin
  return t;
end;
$body$;

Now create two formatting functions:

  • one to format the results for maximum readability
  • and another to format the column headers.

It's dull code, and you needn't read it. You can understand what it does by its effect. It's nice to package away the dull code so that it doesn't clutter the interesting logic of the main plain_timestamp_to_from_timestamp_tz() table function.

create function report_line(
  z             in text,
  ts_plain      in timestamp,
  ts_with_tz_1  in timestamptz,
  ts_with_tz    in timestamptz,
  ts_plain_1    in timestamp)
  returns text
  language plpgsql
as $body$
declare
  t constant text not null :=
      '['||rpad(z, 23)||lpad(utc_offset_display(z), 3)||']'     ||'   '||
           rpad(to_char(ts_plain,     'Dy hh24:mi'        ),  9)||'   '||
           rpad(to_char(ts_with_tz_1, 'Dy hh24:mi TZH:TZM'), 16)||'      '||
           rpad(to_char(ts_with_tz,   'Dy hh24:mi TZH:TZM'), 16)||'   '||
           rpad(to_char(ts_plain_1,   'Dy hh24:mi'        ),  9);
begin
  return t;
end;
$body$;

drop function if exists headers() cascade;
create function headers()
  returns text[]
  language plpgsql
as $body$
declare
  t text[] := array['x', 'x', 'x'];
begin
  t[1] :=
         rpad(' ',           28)           ||'   '||
         rpad('From',         9)           ||'   '||
         rpad('To',  16)                   ||'      '||
         rpad('From',  16)                 ||'   '||
              'To';

  t[2] :=
    '['||rpad('Timezone',    20)||'Offset]'||'   '||
         rpad('ts_plain',     9)           ||'   '||
         rpad('ts_with_tz',  16)           ||'      '||
         rpad('ts_with_tz',  16)           ||'   '||
              'ts_plain';

  t[3] :=
         rpad('-',      28, '-')           ||'   '||
         rpad('-',       9, '-')           ||'   '||
         rpad('-',      16, '-')           ||'      '||
         rpad('-',      16, '-')           ||'   '||
         rpad('-', 9, '-');
  return t;
end;
$body$;

Now create and execute the plain_timestamp_to_from_timestamp_tz() table function thus.

drop function if exists plain_timestamp_to_from_timestamp_tz(boolean) cascade;

create function plain_timestamp_to_from_timestamp_tz(at_utc in boolean)
  returns table(t text)
  language plpgsql
as $body$
declare
  set_timezone      constant text             not null := $$set timezone = '%s'$$;
  tz_on_entry       constant text             not null := current_setting('timezone');

  yyyy              constant int              not null := 2000;
  mm                constant int              not null := 1;
  dd                constant int              not null := 1;
  hh                constant int              not null := 10;
  mi                constant int              not null := 15;
  ss                constant int              not null := 0;
  ts_plain          constant timestamp        not null := make_timestamp  (yyyy, mm, dd, hh, mi, ss);
  ts_with_tz        constant timestamptz      not null := make_timestamptz(yyyy, mm, dd, hh, mi, ss, 'UTC');
  ts_plain_epoch    constant double precision not null := extract(epoch from ts_plain);
  ts_with_tz_epoch  constant double precision not null := extract(epoch from ts_with_tz);
begin
  t := '---------------------------------------------------------------------------------------------';     return next;
  if at_utc then
    t := 'Displaying all results using UTC.';                                                               return next;
  else
    t := 'Displaying each set of results using the timezone in which they were computed.';                  return next;
  end if;
  t := '---------------------------------------------------------------------------------------------';     return next;
  t := '';                                                                                                  return next;

  declare
    ts constant text[] not null := headers();
  begin
    foreach t in array ts loop
                                                                                                            return next;
    end loop;
  end;

  declare
    z                  text   not null := '';
    timezones constant text[] not null := array[
                                                 'Pacific/Pago_Pago',
                                                 'America/Porto_Velho',
                                                 'Atlantic/South_Georgia',
                                                 'UTC',
                                                 'Africa/Tripoli',
                                                 'Asia/Dubai',
                                                 'Pacific/Kiritimati'
                                               ];
  begin
    foreach z in array timezones loop
      execute format(set_timezone, z);

      declare
        ts_with_tz_1        constant timestamptz      not null := ts_plain::timestamptz;
        ts_with_tz_2        constant timestamptz      not null := ts_plain at time zone current_setting('timezone');
        ts_with_tz_1_epoch  constant double precision not null := extract(epoch from ts_with_tz_1);

        ts_plain_1          constant timestamp        not null := ts_with_tz::timestamp;
        ts_plain_2          constant timestamp        not null := ts_with_tz at time zone current_setting('timezone');
        ts_plain_1_epoch    constant double precision not null := extract(epoch from ts_plain_1);

        z_epoch             constant double precision not null := extract(epoch from utc_offset(z));
      begin
        -- Show that "::timestamp" is identical to "at time zone current_setting('timezone')".
        assert (ts_with_tz_2 = ts_with_tz_1), 'Assert #1 failed.';
        assert (ts_plain_2   = ts_plain_1  ), 'Assert #2 failed.';

        -- Show that the expected rules for the conversion of the internal representations,
        -- in both plain timestamp to/from timestamptz directions, hold.
        assert ( ts_with_tz_1_epoch = (ts_plain_epoch   - z_epoch) ), 'Assert #3 failed.';
        assert ( ts_plain_1_epoch   = (ts_with_tz_epoch + z_epoch) ), 'Assert #4 failed.';

        /* Display the internally represented values:
             EITHER: using 'UTC' to show what they "really" are
             OR:     using the timezone in which they were computed to show
                     the intended usability benefit for the local observer. */
        if at_utc then
          execute format(set_timezone, 'UTC');
          -- Else, leave the timezone set to "z".
        end if;
        t := report_line(z, ts_plain, ts_with_tz_1, ts_with_tz, ts_plain_1);                                return next;
      end;
    end loop;
  end;

  execute format(set_timezone, tz_on_entry);
end;
$body$;

select t from plain_timestamp_to_from_timestamp_tz(true);

This is the result:

---------------------------------------------------------------------------------------------
Displaying all results using UTC.
---------------------------------------------------------------------------------------------

                              From        To                    From               To
[Timezone            Offset]   ts_plain    ts_with_tz            ts_with_tz         ts_plain
----------------------------   ---------   ----------------      ----------------   ---------
[Pacific/Pago_Pago      -11]   Sat 10:15   Sat 21:15 +00:00      Sat 10:15 +00:00   Fri 23:15
[America/Porto_Velho    -04]   Sat 10:15   Sat 14:15 +00:00      Sat 10:15 +00:00   Sat 06:15
[Atlantic/South_Georgia -02]   Sat 10:15   Sat 12:15 +00:00      Sat 10:15 +00:00   Sat 08:15
[UTC                    +00]   Sat 10:15   Sat 10:15 +00:00      Sat 10:15 +00:00   Sat 10:15
[Africa/Tripoli         +02]   Sat 10:15   Sat 08:15 +00:00      Sat 10:15 +00:00   Sat 12:15
[Asia/Dubai             +04]   Sat 10:15   Sat 06:15 +00:00      Sat 10:15 +00:00   Sat 14:15
[Pacific/Kiritimati     +14]   Sat 10:15   Fri 20:15 +00:00      Sat 10:15 +00:00   Sun 00:15

The execution finishes without error, confirming that the assertions hold.

Interpretation and statement of the rules

Underlying axiom.

The interpretation of the demonstration's outcome depends on this fact, stated and empirically confirmed in the plain timestamp and timestamptz data types section:

Both a plain timestamp datum and a timestamptz datum have the identical internal representation as the number of seconds from a reference moment. The extract(epoch from t) function, where t is either a plain timestamp value or a timestamptz value, returns this number. Moreover, the result is independent of the session's current TimeZone setting.

The demonstration meets the goals set out in the "The philosophy of the demonstration's design" section:

  • Goal one is met because there are no assert violations: these two properties of the mutual conversion shown in the paragraph that defines this goal are seen to hold.

    IF:
      ts_with_tz_1 ◄— ts_plain::timestamptz
    AND:
      ts_with_tz_2 ◄— ts_plain at time zone current_setting('timezone')
    THEN:
      ts_with_tz_2 == ts_with_tz_1
    


    and:

    IF:
      ts_plain_1 ◄— ts_with_tz::timestamp
    AND:
      ts_plain_2 ◄— ts_with_tz at time zone current_setting('timezone')
    THEN:
      ts_plain_2 == ts_plain_1
    

  • Goal two is met because there are no assert violations: these rules for the mutual conversions are seen to hold.

    ts-with-tz-internal-seconds ◄— ts-plain-internal-seconds - specified-utc-offset-in-seconds
    


    and:

    ts-plain-internal-seconds ◄— ts-with-tz-internal-seconds + specified-utc-offset-in-seconds
    

  • Goal three is met by inspecting the output immediately above and by Invoking the table function again to show each result row using the timezone in which it was computed.

    select t from plain_timestamp_to_from_timestamp_tz(false);
    


    This is the new result:

    ---------------------------------------------------------------------------------------------
    Displaying each set of results using the timezone in which they were computed.
    ---------------------------------------------------------------------------------------------
    
                                  From        To                    From               To
    [Timezone            Offset]   ts_plain    ts_with_tz            ts_with_tz         ts_plain
    ----------------------------   ---------   ----------------      ----------------   ---------
    [Pacific/Pago_Pago      -11]   Sat 10:15   Sat 10:15 -11:00      Fri 23:15 -11:00   Fri 23:15
    [America/Porto_Velho    -04]   Sat 10:15   Sat 10:15 -04:00      Sat 06:15 -04:00   Sat 06:15
    [Atlantic/South_Georgia -02]   Sat 10:15   Sat 10:15 -02:00      Sat 08:15 -02:00   Sat 08:15
    [UTC                    +00]   Sat 10:15   Sat 10:15 +00:00      Sat 10:15 +00:00   Sat 10:15
    [Africa/Tripoli         +02]   Sat 10:15   Sat 10:15 +02:00      Sat 12:15 +02:00   Sat 12:15
    [Asia/Dubai             +04]   Sat 10:15   Sat 10:15 +04:00      Sat 14:15 +04:00   Sat 14:15
    [Pacific/Kiritimati     +14]   Sat 10:15   Sat 10:15 +14:00      Sun 00:15 +14:00   Sun 00:15
    

The results at "Displaying all results using UTC"

These show what is really happening at the level of the internal representation—albeit that you have to deal with a degree of circularity of logic to accept this claim. It's equivalent to looking at the numbers of seconds from 12:00 on 1-Jan-1070. (You could write your own formatter, using the trunc() and mod() built-in functions, to produce the same display.) Of course, only the display of timestamptz values is sensitive to the current value of the session's TimeZone setting.

The "From ts_plain" column shows the same value in each row—as is to be expected. The "To ts_with_tz" column shows a different value in each row, supporting this informal statement of the rule:

  • The timestamptz value is produced by assuming that the to-be-converted plain timestamp value represents local wall-clock time in the timezone in which the conversion is done.

The "From ts_with_tz" column shows the same value in each row—again as is to be expected. The "To ts_plain" column shows a different value in each row, supporting this informal statement of the rule:

  • The plain timestamp value is produced by assuming that the to-be-converted timestamptz's displayed date and time of day, as these would be shown in the timezone at which the conversion is done, jointly represent the target plain timestamp value.

The results at "Displaying each set of results using the timezone in which they were computed"

The displayed text values, of course, represent the same underlying internally represented values as do the displayed values in the results at "Displaying all results using UTC". And, again of course, the text values in the columns for the plain timestamp values are pairwise identical, row by row, in the two sets of results. But the text values in the columns for the timestamptz values are pairwise different, row by row, in the two sets of results—just as they should be to bring the intended usability value of the data type. The rules are (arguably) easier to understand in the second presentation.

The "To ts_with_tz" column shows the same date-and-time value in each row, but the displayed UTC offset at which this is to be understood is different in each row (and equal to the offset that the reigning timezone specifies). The "To ts_with_tz" column shows a different value in each row, supporting this informal statement of the rule:

  • The to-be-converted plain timestamp value is simply decorated with the offset of the timezone in which the conversion is done.

The "To ts_plain" column shows a different value in each row. But this value is identical to the date-and-time value of the to-be-converted timestamptz value, supporting this informal statement of the rule:

  • The offset of the displayed to-be-converted timestamptz value is simply ignored and the target plain timestamp value is set to the to-be-converted value's displayed date-and-time component.

You should aim to be comfortable with these three different ways to state the rules:

  • First in terms of the internally represented seconds and the magnitude of the reigning UTC offset in seconds.
  • Second in terms of the text display of the results using UTC.
  • Third in terms of the text display of the results using the timezones at which the conversions are done.

Notice that the first way to state the rules is by far the most terse and precise—and therefore the most reliable. The other two ways are subject to the limitations of composing, and interpreting, tortuous natural language prose.

  • The philosophy of the demonstration's design
  • The demonstration
  • Interpretation and statement of the rules
    • The results at "Displaying all results using UTC"
    • The results at "Displaying each set of results using the timezone in which they were computed"
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2022 Yugabyte, Inc. All rights reserved.