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.

• TIMESTAMP WITHOUT TIME ZONE
  
  
  Saves only the date and time of day. Any time zone or offset from UTC is ignored.
• TIMESTAMP WITH TIME ZONE
  
  
  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.

Remember, it TIMESTAMP WITHOUT TIME ZONE

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.

TIMESTAMP WITHOUT TIME ZONE

For the TIMESTAMP WITHOUT TIME ZONE

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.

      

        
        
        
      

    

TIMESTAMP WITH TIME ZONE

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

instead TIMESTAMP WITHOUT 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?

• Java SE 8 , Java SE 9 and then
  • Built in.
  • Part of the standard Java API with a combined implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Most of the java.time functionality goes back to Java 6 and 7 in ThreeTen-Backport .
• Android
  • The ThreeTenABP project adapts the ThreeTen-Backport (mentioned above) specifically for Android.
  • See. How to use ... ThreeTenABP .

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 .