Timestamp with vs without time zone.

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Timestamp with vs without time zone.
Date: 2021-09-21 06:00:18
Message-ID: CAGuHJrPKdrXGAFkzE4tXd3b5uKe47WCRdV3LNrYi_HBjYYa+OA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am hoping to get some clarification on timestamp with time zone.

My understanding is that timestamp with time zone stores data in UTC
but displays it in your time zone. Does this also work on queries? If
I query between noon and 2:00 PM on some date in time zone XYZ does pg
translate the query to UTC before sending it to the server?

To provide context I have the following situation.

I have a data file to import. All the dates in the time zone
pacific/auckland. My app reads the data , does some processing and
cleaning up and then saves it to the database.

The language I am using creates the time data type with the right time
zone. The processing is being done on a server which is on UTC, the
database server is also on UTC. I am pretty sure the ORM isn't
appending "at time zone pacific/Auckland" to the data when it appends
it to the database.

So does the database know the timestamp is in auckland time when the
client is connecting from a server on UTC?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Niels Jespersen 2021-09-21 07:22:00 SV: Possibilities for optimizing inserts across oracle_fdw foreign data wrapper
Previous Message Peter J. Holzer 2021-09-20 20:47:18 Re: Azure Postgresql High connection establishment time