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);
How do we store postgres datetime objects in java pojo classes for json objects?
It depends.
TIMESTAMP WITH TIME ZONE, use the java.time.Instant class.TIMESTAMP WITHOUT TIME ZONE, use the java.time.LocalDateTime class.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.
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.
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.
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.
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.
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.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.
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.
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(!).
ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;
To get back to UTC, extract a 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.
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:
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.
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.
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.
Storage.
myPreparedStatement.setObject( … , instant ) ;
Retrieval.
Instant instant = myResultSet.getObject( … , Instant.class ) ;
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.
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.
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.
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.
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.
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.
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.
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.
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
Thanks. I have used java.sql Timestamp and it works fine. I couldn't see LocalDatetime supported by Json Jackson library. – JEE_program Jul 3 at 21:47