Postgres offers two date-time types per the SQL standard. The standard barely touches on the topic unfortunately, so the behavior described here is specific to Postgres. Other databases may behave differently.
TIMESTAMP WITHOUT TIME ZONE
Stores just a date and a time-of-day. Any time zone or offset-from-UTC passed is ignored.TIMESTAMP WITH TIME ZONE
First adjusts the passed date+time using its passed zone/offset to get a value in UTC. The passed zone/offset is then discarded after the adjustment is made; if needed, you must store that original zone/offset information in a separate column yourself.
Be aware that TIMESTAMP WITHOUT TIME ZONE does not represent an actual moment, does not store a point on the timeline. Without the context of a zone or offset, it has no real meaning. It represents a range of possible moments over a span of about 26-27 hours. Good for problems such as storing a appointment far enough out in the future that the time zone rules may be changed before its arrival. Also good for problems such as “Christmas starts after midnight on December 25 this year”, where you mean a different moment in time in each zone with each zone westward arriving later and later in succession.
When recording actual moments, specific points on the timeline, use TIMESTAMP WITH TIME ZONE.
The modern approach in Java uses the java.time classes rather than either the Joda-Time library or the troublesome old legacy date-time classes bundled with the earliest versions of Java.
TIMESTAMP WITHOUT TIME ZONE
For TIMESTAMP WITHOUT TIME ZONE, the equivalent class in java.time is LocalDateTime for a date and time-of-day without any offset or zone.
As others pointed out, some tools may dynamically apply a time zone to the retrieved value in a misguided and confusing albeit well-intentioned anti-feature. The following Java code will retrieve your true date-time value sans zone/offset.
Requires a JDBC driver compliant with JDBC 4.2 or later to directly work with java.time types.
LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ; // Retrieving a `TIMESTAMP WITHOUT TIME ZONE` value.
To insert/update database:
myPreparedStatement.setObject( … , ldt ) ; // Inserting/updating a `TIMESTAMP WITHOUT TIME ZONE` column.
TIMESTAMP WITH TIME ZONE
Your discussion of time zones suggests you are concerned with actual moments on the timeline. So you should absolutely be using TIMESTAMP WITH TIME ZONE instead of TIMESTAMP WITHOUT TIME ZONE. You should not be messing about with Daylight Saving Time (DST) gaps and such. Let java.time and Postgres do that work for you, with much better code already written and tested.
To retrieve:
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.
To insert/update database:
myPreparedStatement.setObject( … , zdt ) ; // Inserting/updating a `TIMESTAMP WITH TIME ZONE` column.
To retrieve from database:
Instant instant = myResultSet.getObject( … , Instant.class ) ;
E.g. when I store LocalDateTime 2015-03-29 02:30:00 from the Europe/London timezone
No, no, no. Do not work this way. You are misusing the types of both Java and Postgres.
If the user entered 2015-03-29 02:30:00 intended to represent a moment in Europe/London time zone, then parse as a LocalDateTime and immediately apply a ZoneId to get a ZonedDateTime.
To parse, replace the SPACE in the middle with a T to comply with ISO 8601 standard formatting used by default in the 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 that same moment in UTC, extract a Instant. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).
Instant instant = zdt.toInstant() ;
Pass the instant via JDBC for storage in the database in a TIMESTAMP WITH TIME ZONE.
myPreparedStatement.setObject( … , instant ) ;
Use objects, not strings
Note that all my code here is using java.time objects to exchange data with the database. Always use these objects rather than mere strings for exchanging date-time values.
About java.time
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
Where to obtain the java.time classes?
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
