Inconsistent date format while retrieving from postgres using java 1.7

Question

I'm facing this unique issue while retrieving date column values from postgres and export as a CSV using JDK 1.7 Following is a sample output

ID,      Date Created,        Date Modified 
816271,  8/8/2013 14:35       2/2/2015 16:47
830322   13/08/2013 11:48 AM  2/2/2015 16:48
1128312  10/2/2015 16:53      10/2/2015 16:53
1129465  12/2/2015 16:23      12/2/2015 16:23
1130482  16/02/2015 4:28 PM   15/06/2015 7:01 AM
1019527  19/08/2014 4:40 AM   23/02/2015 12:14 PM
1134334  23/02/2015 8:38 AM   4/6/2015 5:16

The problem is, I see that AM/PM being appended those date values where the DAY part is greater than 12. When I look into the database I don't see any AM/PM. In my DO, I've just declared the variable as Date.

Please let me know why this inconsistent formatting happens.

thanks

Following is how I set the date into my DO.

public void setCreatedDate(Date createdDate) { 
  if (createdDate == null) { 
    this.mCreatedDate = createdDate; return; 
  }
  this.mCreatedDate = new Date(createdDate.getTime());
} 

I'm not using any formatting code at all. Even there is one, I'm not sure why it is not applied to all record

Answer 1

You need to understand that a date-time value stored in a database using a date-time data type has no format. What you are seeing are String representations of that date-time value generated for the convenient viewing by humans. The String is not the date-time.

So your formatting issue with "AM/PM" relates to some code generating that string outside of Postgres. You do not show us that code, so we cannot directly resolve the Question. But you can avoid the problem in the first place if you consciously work with date-time values/objects rather than Strings.

Storing date-time in Postgres

In Postgres, you should generally be using the TIMESTAMP WITH TIME ZONE data type. This type does not actually keep the time zone. Rather it has respect for the time zone, using any passed offset or time zone information accompanying data input to adjust to UTC. The result is then stored in the database. After adjustment, Postgres discards the original offset or time zone info.

Retrieving date-time from Postgres

When retrieving data (a SELECT), you may get a date-time value or you may get a String, depending on the client app (pgAdmin, psql, SQuirreL SQL Client, and such) or your database driver (JDBC and such). If getting a String, an adjustment to some time zone may have been made on your behalf, but that String is not the date-time value. If getting a date-time value, stick with that value for your work rather than converting to strings. In JDBC, that means using java.sql.Timestamp objects, for example.

Java date-time frameworks

If using Java 8 or later technology, you should make use of the new java.time package. If not possible, use the Joda-Time library. Try to avoid java.util.Date/.Calendar & java.text.SimpleDateFormat as they are troublesome and confusing.

Example

Below is a full example of extracting a java.sql.Timestamp from Postgres 9.4, then using java.time or Joda-Time to work with the value.

Data Loss with Joda-Time & java.util.Date

Note that Joda-Time (like java.util.Date) is limited to millisecond precision of fractional seconds. Postgres resolves to microseconds. So converting from Postgres to Joda-Time/java.util.Date means likely data loss. With java.time, no problem as it resolves to nanoseconds.

Code

Written in Java 8 Update 51, using the postgresql-9.4-1201.jdbc41.jar driver with Postgres 9.4.x on Mac OS X Mountain Lion.

String message = "Example of fetching Timestamp from Postgres.";
StringBuilder sql = new StringBuilder();
sql.append( "SELECT now() " + "\n" );
sql.append( ";" );
java.sql.Timestamp ts = null;
try ( Connection conn = DatabaseHelper.instance().connectionInAutoCommitMode() ; 
        PreparedStatement pstmt = conn.prepareStatement( sql.toString() ); ) {
    try ( ResultSet rs = pstmt.executeQuery(); ) {
        // Extract data from result set
        int count = 0;
        while ( rs.next() ) {
            count ++;
            ts = rs.getTimestamp( 1 );
        }
    }

} catch ( SQLException ex ) {
    logger.error( "SQLException during: " + message + "\n" + ex );
} catch ( Exception ex ) {
    logger.error( "Exception during: " + message + "\n" + ex );

}

java.sql.Timestamp

Beware of how the old Java date-time classes implicitly apply your JVM’s current default time zone. While intended to be helpful, it creates no end of confusion. The time zone seen when running this code is America/Los_Angeles which has an offset of −07:00.

String output_SqlTimestamp = ts.toString();  // Confusingly applies your JVM’s current default time zone.

java.time

Use java.time in Java 8 and later.

// If you have Java 8 or later, use the built-in java.time package.

java.time.Instant instant = ts.toInstant();
java.time.ZoneId zoneId = ZoneId.of( "America/Montreal" );
java.time.ZonedDateTime zdt = java.time.ZonedDateTime.ofInstant( instant , zoneId );

String output_UTC = instant.toString();
String output_Montréal = zdt.toString();

System.out.println( "output_SqlTimestamp: " + output_SqlTimestamp );  
System.out.println( "output_UTC: " + output_UTC );
System.out.println( "output_Montréal: " + output_Montréal );

Joda-Time

Before Java 8, use Joda-Time.

// Before Java 8, use Joda-Time. (Joda-Time was the inspiration for java.time.)
// IMPORTANT: Joda-Time, like java.util.Date, is limited to milliseconds for fraction of a second. So you may experience data loss from a Postgres date-time value with microseconds.

org.joda.time.DateTime dateTimeMontréal = new org.joda.time.DateTime( ts.getTime() , DateTimeZone.forID( "America/Montreal" ) );  // WARNING: Data lost with microseconds truncated to milliseconds.
org.joda.time.DateTime dateTimeUtc = dateTimeMontréal.withZone( DateTimeZone.UTC );

String output_Joda_dateTimeMontréal = dateTimeMontréal.toString();
String output_Joda_dateTimeUtc = dateTimeUtc.toString();

System.out.println( "output_Joda_dateTimeMontréal: " + output_Joda_dateTimeMontréal );
System.out.println( "output_Joda_dateTimeUtc: " + output_Joda_dateTimeUtc );

When run.

output_SqlTimestamp: 2015-08-24 12:46:06.979144
output_UTC: 2015-08-24T18:46:06.979144Z
output_Montréal: 2015-08-24T14:46:06.979144-04:00[America/Montreal]
output_Joda_dateTimeMontréal: 2015-08-24T14:46:06.979-04:00
output_Joda_dateTimeUtc: 2015-08-24T18:46:06.979Z