Multi tool use

Datetime fields in json

How do we store postgres datetime objects in java pojo classes for json objects? I am trying to sort them and want to check if I should be comparing datetime or strings? Date compareTo doesn't work but strings comparTo works fine for datetime objects

private Date fieldA;
private Date fieldB;

fieldA.compareTo(fieldB);

Date compareTo

java.time

2 Answers
2

tl;dr

How do we store postgres datetime objects in java pojo classes for json objects?

It depends.

TIMESTAMP WITH TIME ZONE

java.time.Instant

TIMESTAMP WITHOUT TIME ZONE

java.time.LocalDateTime

As for JSON, there are no JSON data types for date-time values. Generate strings in standard ISO 8601 format.

I am trying to sort them

The java.time classes know how to sort themselves, implementing the Comparable interface.

Comparable

if I should be comparing datetime or strings?

Always use smart objects, not dumb strings. That is why you have JDBC technology and JDBC drivers.

Date compareTo doesn't work

Never use the java.util.Date class. Never use the java.sql.Date class. Use only java.time classes.

java.util.Date

java.sql.Date

strings comparTo works fine for datetime objects

Nope. Date-time strings can come in all kinds of formats, using all kinds of human languages and cultural norms, with various time zones or offsets-from-UTC applied. Strings are not appropriate for sorting date-time values. Use smart java.time objects, not dumb strings.

Or do your sorting on the database side, where Postgres is optimized for such chores.

private Date fieldA; private Date fieldB;

Make that:

private Instant fieldA, fieldB ;
…
boolean isAEarlier = fieldA.isBefore( fieldB ) ;
boolean isAtheSame = fieldA.equals( fieldB ) ; // Note that some other java.time classes have `isEqual` method as well as `equals` method.
boolean isALater = fieldA.isAfter( fieldB ) ;
boolean isAEqualToOrLaterThan = ( ! fieldA.isBefore( fieldB ) ) ; // "Is equal to or later than" is a common use-case. "Not before" is a logical shortcut with the same effect.


java.time

The Date class is now legacy, part of the terribly troublesome old date-time classes that were supplanted by the java.time classes years ago. Never use Date, Calendar, SimpleDateFormat, and such.

Date

Date

Calendar

SimpleDateFormat

Your Question is a duplicate of many others, so I'll be brief here. Search Stack Overflow to learn more.

For the database column type TIMESTAMP WITH TIME ZONE defined in the SQL standard and used in Postgres, that represents a moment, a specific point on the timeline.

TIMESTAMP WITH TIME ZONE

In Postgres, this type has a resolution of microseconds and is always in UTC. Any inputs with an indicator of time zone or offset-from-UTC are adjusted into UTC, and the zone/offset then discarded. So the type is a bit of a misnomer, as the original zone/offset is forgotten and the stored value is always in UTC. Other databases may vary in this behavior, so beware, as the SQL spec barely touches on the subject of date-time.

Beware that when using tools other than JDBC, your tool may be injecting a time zone or offset-from-UTC after retrieving the stored UTC value; this can be quite misleading and confusing to a novice (and is an unfortunate design decision in my opinion).

In Java, generally best to work in UTC. As a programmer, learn to think, store, and exchange moments as UTC. Generally, use the Instant class for this. For defining member variables in your classes, Instant is your go-to class.

Instant

Instant

Instant instant = Instant.now() ; // Capture the current moment in UTC, with a resolution as fine as nanoseconds.


You may want to truncate any nanoseconds to microseconds to match retrieved values from Postgres. Specify resolution with ChronoUnit.

ChronoUnit

Instant instant = Instant.now().truncatedTo( ChronoUnit.MICROS ) ;


For presentation to the user in their desired/expected time zone, assign a ZonedId to get a ZonedDateTime.

ZonedId

ZonedDateTime

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

continent/region

America/Montreal

Africa/Casablanca

Pacific/Auckland

EST

IST

ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;


To get back to UTC, extract a Instant.

Instant

Instant instant = zdt.toInstant() ;


To generate localized text representing the value of the ZonedDateTime object, use DateTimeFormatter. Search Stack Overflow for much more info.

ZonedDateTime

DateTimeFormatter

The database type TIMESTAMP WITHOUT TIME ZONE purposely lacks any concept of time zone or offset-from-UTC. As such it does not represent a moment, is not a point on the timeline, and is not what you likely want in a business app except when:

TIMESTAMP WITHOUT TIME ZONE

In Postgres, any zone or offset accompanying input is ignored. The date and the time-of-day are stored as-is with no adjustment.

The matching type in Java is LocalDateTime.

LocalDateTime

The “Local” in this class name does not mean “a particular locality”. Just the opposite! It means every locality, or any locality, but not a particular locality. If you do not understand this, do some study, read the class doc, and search Stack Overflow.

Database

Use smart objects rather than dumb strings to exchange date-time values with your database.

As of JDBC 4.2, you can directly exchange java.time objects with the database. Never use java.sql.Timestamp, java.sql.Date, and java.sql.Time.

java.sql.Timestamp

java.sql.Date

java.sql.Time

Storage.

myPreparedStatement.setObject( … , instant ) ;


Retrieval.

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


JSON

The JSON spec defines very few data types, and none of them are date-time related. You are on your own there. Ditto for XML.

When serializing date-time values as text, use the standard ISO 8601 formats. These are designed to be practical and useful, and to avoid ambiguities. They are designed to be easy to parse by machine, while also being easy to read by humans across cultures.

The java.time classes use these standard formats by default when parsing/generating date-time strings. Just call parse and toString on the various classes.

parse

toString

Instant instant = Instant.parse( "2018-01-23T01:23:45.123456Z" ) ;

String output = instant.toString() ;


The ISO 8601 format for a moment happen to be similar to the usual SQL format except that in SQL uses a SPACE in the middle rather than a T. That fact is largely irrelevant as you should be using objects rather than strings between Java and your database, as mentioned above.

T

Half-Open

Related to the topic of comparing… When working with spans of time, learn to consistently use the Half-Open approach where the beginning is inclusive while the ending is exclusive. Search Stack Overflow to learn more.

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.

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.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

java.sql.*

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.

Interval

YearWeek

YearQuarter

You should use the java.time.LocalDateTime class. This is the new (Java 8) class for representing a date and time without any specific time zone or offset.

java.time.LocalDateTime

In other words, you can think of it as holding a year, month, day, hour, minute, second and millisecond. But because there's no time zone or offset specified, it doesn't actually correspond to a particular Instant - that is, a particular moment in time.

Instant

It seems to me that of all the Java 8 date/time related classes, this is the one that's closest in intent to what you'd store in a database's DateTime field.

Further reading: Basil Bourque's answer to this question

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.