DST issues storing Joda LocalDateTime in PostgreSQL "timestamp" column

In our application, we store dates that are in many different time zones. We decided to use the Joda type so LocalDateTime

that the user always gets literally whatever they typed in first. This is exactly what we need.

Internally, we know which timezone the user belongs to - so when they enter datetime, we do the check like this:



If this time-date does not exist in their time zone (this is in the space of the day's savings), we display an error message that the date is incorrect, thereby preventing the storage of incorrect date-data in the database.

The timestamp column is used for storage . Problems arise when the user-entered datetime exists in their time zone but does not exist in the database time zone (Europe / Berlin). For example. when I store LocalDateTime 2015-03-29 02:30:00 from Europe / London timezone (it really is - in London the gap is between 01:00 and 02:00), PostgreSQL shifts the hour by 1 and stores it as 2015-03- 29 03:30:00 .

What to do? Is there a way to tell PostgreSQL to do nothing about timezones and just store the data literally like Joda does? (except for saving them as strings;))


source to share

2 answers


Equivalent in PostgreSQL 7.3 and up timestamp without time zone

. This data type is not a time zone. It only stores date and time. If you find it skewed, it could be due to the code or tools you are using to store or retrieve data.

Note that before version 7.3 is timestamp

equivalent timestamp with timezone

. This is mentioned in the first note in the documentation here .



Postgres offers two types of date-date in the SQL standard. Unfortunately, the standard barely covers this topic, so the behavior described here is Postgres specific. Other databases can behave differently.


    Saves only the date and time of day. Any time zone or offset from UTC is ignored.

    First adjusts the elapsed date + time using the passed zone / offset to get the UTC value. The traversed zone / offset is then discarded after the adjustment is made; if necessary, you must save this original zone / offset information in a separate column yourself.


does not represent the actual moment, it does not store a point in the timeline. Without zone context or offset, it has no real meaning. It represents the range of possible moments within approximately 26-27 hours. Good for problems such as keeping a pre-scheduled appointment in the future, where the time zone rules might change before it arrives. Also good for problems like "Christmas starts after midnight on December 25 this year" where you mean a different point in time in each zone with each zone westward arriving later and later.

When recording the actual points at specific points on the timeline, use TIMESTAMP WITH TIME ZONE


The modern approach in Java is using the java.time classes, not the Joda-Time library or the nasty old legacy time classes associated with the earliest versions of Java.



equivalent class in java.time, it is LocalDateTime

for date and time of day without any offset or zone.

As others have pointed out, some tools can dynamically apply the timezone to the resulting value in erroneous and confusing, albeit well-intentioned, anti-characteristics. The following Java code will return your true datetime value without zone / offset.

A JDBC driver compatible with JDBC 4.2 or later is required to work directly with java.time types.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;  // Retrieving a `TIMESTAMP WITHOUT TIME ZONE` value.


Insert / update database:

myPreparedStatement.setObject( … , ldt ) ;  // Inserting/updating a `TIMESTAMP WITHOUT TIME ZONE` column.



Your discussion of time zones assumes that you are dealing with actual points in the timeline. Therefore, you should use TIMESTAMP WITH TIME ZONE


. You shouldn't mess with spaces in Daylight Saving Time (DST) etc. Let java.time and Postgres do it for you, with much better code already written and tested.

To obtain:

Instant instant = myResultSet.getObject( … , Instant.class ) ;  // Retrieving a `TIMESTAMP WITH TIME ZONE` value in UTC.
ZonedDateTime zdt = instant.atZone( ZoneId.of( "Africa/Tunis" ) ) ;  // Adjusting from a UTC value to a specific time zone.


Insert / update database:

myPreparedStatement.setObject( … , zdt ) ;  // Inserting/updating a `TIMESTAMP WITH TIME ZONE` column.


To retrieve from the database:

Instant instant = myResultSet.getObject( … , Instant.class ) ;


eg. when i store LocalDateTime 2015-03-29 02:30:00 from Europe / London timezone

No no no. Don't work this way. You are using both Java and Postgres types incorrectly .

If the user has entered 2015-03-29 02:30:00

intended to represent a moment in a Europe/London

timezone, then parse it as LocalDateTime

and apply immediately ZoneId

to get ZonedDateTime


To parse, replace the SPACE in the middle T

with the ISO 8601 standard formatting used by default in java.time classes.

String input = "2015-03-29 02:30:00".replace( " " , "T" ) ;
LocalDateTime ldt = LocalDateTime.parse( input ) ;
ZoneId z = ZoneId.of( "Europe/London" ) ;
ZonedDateTime zdt = ldt.atZone( z ) ; 


To see the same moment in UTC, extract Instant

. The class Instant

represents a moment on the timeline in UTC with nanosecond resolution (up to nine (9) decimal places).

Instant instant = zdt.toInstant() ;


Pass instant access via JDBC for database storage in TIMESTAMP WITH TIME ZONE


myPreparedStatement.setObject( … , instant ) ;


Use objects, not strings

Note that all my code here uses java.time objects to communicate with the database. Always use these objects, not just strings, to exchange date and time values.

About java.time

The java.time framework is built into Java 8 and later. These classes supersede the nasty old legacy datetime classes such as java.util.Date

, Calendar

and . SimpleDateFormat

The Joda-Time project , now in maintenance mode , advise moving to the java.time classes .

To find out more, see the Oracle Tutorial AND search Stack Overflow for many examples and explanations. Specification JSR 310 .

Where can I get the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is proof of possible future additions to java.time. Here you can find useful classes, such as Interval

, YearWeek

, YearQuarter

and longer .



All Articles