Developer Blog
59

Dealing with timezones: Postgres vs. Oracle Date/Time Types

Avatar-Foto Daniela Pressmar
1 MIN LESEDAUER
0 KOMMENTARE
IN POCKET SPEICHERN
11.12.2023 –

Did you know that the Postgres datatype „timestamp with timezone“ is not really saving the timezone information? PostgreSQL converts the value into a UTC value and stores that in the table. But the origin timezone information is lost forever.

In Oracle also exists a datatype called „TIMESTAMP WITH TIME ZONE“. This data type stores the date and time along with the time zone information (offset or timezone name). Those difference is important to know as it might cause unexpected side effects.
 
The following tables help to get a better understanding of the Postgres and Oracle data types for date and timezone data.
 

Feature
Date Time Millis TZ
❌ ✅ ❌ ❌
Data Type
Postgres Oracle
TIME <none>
Example Example how the postgres database will deal with datatype TIME

 

Feature
Date Time Millis TZ
✅ ❌ ❌ ❌
Data Type
Postgres Oracle
Date <none>
Example Example how the postgres database will deal with datatype DATE

 

Feature
Date Time Millis TZ
✅ ✅ ❌ ❌
Data Type
Postgres Oracle
TIMESTAMP(0) DATE
Example Example how the postgres database will deal with datatype DATE

 

Feature
Date Time Millis TZ
✅ ✅ ✅ ❌
Data Type
Postgres Oracle
TIMESTAMP TIMESTAMP
Example Example how the postgres and oracle database will deal with datatype TIMESTAMP

 

Feature
Date Time Millis TZ
✅ ✅ ✅ ✅ UTC/DB Time
Data Type
Postgres Oracle
TIMESTAMP WITH TIME ZONE
(short: TIMESTAMPTZ)		 TIMESTAMP WITH LOCAL TIME ZONE
–
Example Postgres:

Example how the postgres database will deal with datatype TIMESTAMP WITH TIMEZONE

Oracle:

Example how the oracle database will deal with datatype TIMESTAMP WITH LOCAL TIMEZONE

 

Feature
Date Time Millis TZ
✅ ✅ ✅ ✅ Offset/Region
Data Type
Postgres Oracle
<not available>
Fallback: TIMESTAMP + VARCHAR (containing TZ)		 TIMESTAMP WITH TIME ZONE
–
Example Oracle: OffsetExample how the oracle database will deal with datatype TIMESTAMP WITH TIMEZONE when providing the offset

Oracle: Region name (recommended as Daylight Saving is automatically taken into account)

Example how the oracle database will deal with datatype TIMESTAMP WITH TIMEZONE when providing the region

Image Credits: all graphics are own illustrations

 

Interested in more articles about „Dealing with timezones“? Here are all the posts:
Zurück zur Übersicht

Kommentar verfassen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

*Pflichtfelder

*