[postgresql] Difference between timestamps with/without time zone in PostgreSQL

Timestamptz vs Timestamp

The timestamptz field in Postgres is basically just the timestamp field where Postgres actually just stores the “normalised” UTC time, even if the timestamp given in the input string has a timezone.

If your input string is: 2018-08-28T12:30:00+05:30 , when this timestamp is stored in the database, it will be stored as 2018-08-28T07:00:00.

The advantage of this over the simple timestamp field is that your input to the database will be timezone independent, and will not be inaccurate when apps from different timezones insert timestamps, or when you move your database server location to a different timezone.

To quote from the docs:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone zone. To give a simple analogy, a timestamptz value represents an instant in time, the same instant for anyone viewing it. But a timestamp value just represents a particular orientation of a clock, which will represent different instances of time based on your timezone.

For pretty much any use case, timestamptz is almost always a better choice. This choice is made easier with the fact that both timestamptz and timestamp take up the same 8 bytes of data.

source: https://hasura.io/blog/postgres-date-time-data-types-on-graphql-fd926e86ee87/

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to types

Cannot invoke an expression whose type lacks a call signature How to declare a Fixed length Array in TypeScript Typescript input onchange event.target.value Error: Cannot invoke an expression whose type lacks a call signature Class constructor type in typescript? What is dtype('O'), in pandas? YAML equivalent of array of objects in JSON Converting std::__cxx11::string to std::string Append a tuple to a list - what's the difference between two ways? How to check if type is Boolean

Examples related to timestamp

concat yesterdays date with a specific time How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Pandas: Convert Timestamp to datetime.date Spark DataFrame TimestampType - how to get Year, Month, Day values from field? What exactly does the T and Z mean in timestamp? What does this format means T00:00:00.000Z? Swift - iOS - Dates and times in different format Convert timestamp to string Timestamp with a millisecond precision: How to save them in MySQL

Examples related to timezone

How to set the timezone in Django? How to convert Moment.js date to users local timezone? What does this format means T00:00:00.000Z? How do I get the current timezone name in Postgres 9.3? MySQL JDBC Driver 5.1.33 - Time Zone Issue Python get current time in right timezone Symfony2 and date_default_timezone_get() - It is not safe to rely on the system's timezone settings PHP date() with timezone? How to store a datetime in MySQL with timezone info Java SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'") gives timezone as IST