Menu
Let's say that we have an appointment in Milan Italy happening on 01/23/2021 21:00 "Europe/Rome". This appointment is saved to the database in UTC in a column of a type akin to the SQL-standard type TIMESTAMP WITH TIME ZONE.
Now a user living in New York US needs to understand when this appointment will take place. We can show that user the date-time converted to "America/New_York" time zone, or instead, in "Europe/Rome" TZ. Once the user will fly from New York to Milan, he will find both info useful.
The point is to store everything converted into the same TZ reference (UTC), and the manipulate the date-time depending on the goal you have using the java.time framework bundled with modern Java.
Makes sense or there is something wrong/missing?
231
PostgreSQL timestamp is used to store date and time format data into the database, timestamp automatically updates the timestamp each time when row was modified or inserted into the table. If in some case if time zone of server changes it will not effect on actual data that we have stored into the database.
Any strategy for storing date-and-time data in PostgreSQL should, IMO, rely on these two points: Your solution should never depend on the server or client timezone setting. Currently, PostgreSQL (as most databases) doesn't have a datatype to store a full date-and-time with timezone.
Below figure shows the example of now function in PostgreSQL: The time of day function is used to select todays day, date and time format with timezone. The below figure shows the example of a time of day function in PostgreSQL. The current timestamp function is used to select the current date and time in PostgreSQL.
Makes sense or there is something wrong/missing?
It depends on the kind of appointment.
There are two kinds of appointments:
If we are booking the launch of a rocket, for example, we do not care about the date and the time-of-day. We only care about the moment when (a) the heavens align, and (b) we expect favorable weather.
If in the intervening time the politicians change the rules of the time zone in use at our launch site or at our offices, that has no effect on our launch appointment. If politicians governing our launch site adopt Daylight Saving Time (DST), the moment of our launch remains the same. If the politicians governing our offices decide to change the clock a half-hour earlier because of diplomatic relations with a neighboring country, the moment of our launch remains the same.
For such an appointment, yes, your approach would be correct. You would record the appointment in UTC using a column of type TIMESTAMP WITH TIME ZONE. Upon retrieval, adjust into any time zone the user prefers.
Databases such as Postgres use any time zone info accompanying an input to adjust into UTC, and then disposes of that time zone info. When you retrieve the value from Postgres, it will always represent a date with time-of-day as seen in UTC. Beware, some tooling or middleware may have the anti-feature of applying some default time zone between retrieval from database and delivery to you the programmer. But be clear: Postgres always saves and retrieves values of type TIMESTAMP WITH TIME ZONE in UTC, always UTC, and offset-from-UTC of zero hours-minutes-seconds.
Here is some example Java code.
LocalDate launchDateAsSeenInRome = LocalDate.of( 2021 , 1 , 23 ) ;
LocalTime launchTimeAsSeenInRome = LocalTime.of( 21 , 0 ) ;
ZoneId zoneEuropeRome = ZoneId.of( "Europe/Rome" ) ;
// Assemble those three parts to determine a moment.
ZonedDateTime launchMomentAsSeenInRome = ZonedDateTime.of( launchDateAsSeenInRome , launchTimeAsSeenInRome , zoneEuropeRome ) ;
launchMomentAsSeenInRome.toString(): 2021-01-23T21:00+01:00[Europe/Rome]
To see the same moment in UTC, convert to an Instant. An Instant object always represents a moment as seen in UTC.
Instant launchInstant = launchMomentAsSeenInRome.toInstant() ; // Adjust from Rome time zone to UTC.
launchInstant.toString(): 2021-01-23T20:00:00Z
The Z on the end of the above string example is standard notation for UTC, and is pronounced “Zulu”.
Unfortunately the JDBC 4.2 team neglected to require support for either Instant or ZonedDateTime types. So your JDBC driver may or may not be able to read/write such objects to your database. If not, simply convert to OffsetDateTime. All three types represent a moment, a specific point on the timeline. But OffsetDateTime has support required by JDBC 4.2 for reasons that escape me.
OffsetDateTime odtLaunchAsSeenInRome = launchMomentAsSeenInRome.toOffsetDateTime() ;
Writing to database.
myPreparedStatement.setObject( … , odtLaunchAsSeenInRome ) ;
Retrieval from database.
OffsetDateTime launchMoment = myResultSet.getObject( … , OffsetDateTime.class ) ;
Adjust to the New York time zone desired by your user.
ZoneId zoneAmericaNewYork = ZoneId.of( "America/New_York" ) ;
ZonedDateTime launchAsSeenInNewYork = launchMoment.atZoneSameInstant( zoneAmericaNewYork ) ;
launchAsSeenInNewYork.toString(): 2021-01-23T15:00-05:00[America/New_York]
You can see all of the above code run live at IdeOne.com.
By the way, tracking past events are also treated as a moment. When did the patient actually arrive for appointment, when did the customer pay the invoice, when did a new hire sign their documents, when did the server crash… all these are tracked as a moment in UTC. As discussed above, usually that would be an Instant, though ZonedDateTime & OffsetDateTime also represent a moment. For the database use TIMESTAMP WITH TIME ZONE (not WITHOUT).
I expect most business-oriented apps are focused on appointments of the other type, where we aim at a date with a time-of-day rather than a specific moment.
If the user makes an appointment with their health care provider to review the results of a test, they do so for a particular time-of-day on that date. If in the meantime the politicians change the rules of their time zone, moving the clock ahead or behind an hour or half-hour or any other amount of time, the date and time-of-day of that medical appointment remain the same. In actuality, the point of the timeline of the original appointment will be changed after the politicians change the time zone, shifting to an earlier/later point on the timeline.
For such appointmentss, we do not store the date and time-of-day as seen in UTC. We do not use the database column type TIMESTAMP WITH TIME ZONE.
For such appointments, we store the date with time-of-day without any regard to time zone. We use a database column of type TIMESTAMP WITHOUT TIME ZONE (notice WITHOUT rather than WITH). The matching type in Java is LocalDateTime.
LocalDate medicalApptDate = LocalDate.of( 2021 , 1 , 23 ) ;
LocalTime medicalApptTime = LocalTime.of( 21 , 0 ) ;
LocalDateTime medicalApptDateTime = LocalDateTime.of( medicalApptDate , medicalApptTime ) ;
Write that to the database.
myPreparedStatement.setObject( … , medicalApptDateTime ) ;
Be clear on this: a LocalDateTime object does not represent a moment, is not a specific point on the timeline. A LocalDateTime object represents a range of possible moments along about 26-27 hours of the timeline (the range of time zones around the globe). To give real meaning to a LocalDateTime, we must associate an intended time zone.
For that intended time zone, use a second column to store the zone identifier. For example, the strings Europe/Rome or America/New_York. See list of zone names.
ZoneId zoneEuropeRome = ZoneId.of( "Europe/Rome" ) ;
Write that to the database as text.
myPreparedStatement.setString( … , zoneEuropeRome ) ;
Retrieval. Retrieve the zone name as text, and instantiate a ZoneId object.
LocalDateTime medicalApptDateTime = myResultSet.getObject( … , LocalDateTime.class ) ;
ZoneId medicalApptZone = ZoneId.of( myResultSet.getString( … ) ) ;
Put those two pieces together to determine a moment represented as a ZonedDateTime object. Do this dynamically when you need to schedule a calendar. But do not store the moment. If the politicians redefine the time zone(s) in the future, a different moment must be calculated then.
ZonedDateTime medicalApptAsSeenInCareProviderZone = ZonedDateTime.of( medicalApptDateTime , medicalApptZone ) ;
The user is traveling to New York US. They need to know when to call the health care provider in Milan Italy according to the clocks on the wall in their temporary location of New York. So adjust from one time zone to another. Same moment, different wall-clock time.
ZoneId zoneAmericaNewYork = ZoneId.of( "America/New_York" ) ;
ZonedDateTime medicalApptAsSeenInNewYork = medicalApptAsSeenInCareProviderZone.withZoneSameInstant( zoneAmericaNewYork ) ;
Be aware that if the rules of your desired time zones may be changing, you must update the copy of time zone definitions on your computers.
Java contains its own copy of the tzdata, as does the Postgres database engine. And your host operating system as well. This particular code shown here requires only Java to be up-to-date. If you use Postgres to make time zone adjustments, its tzdata must also be up-to-date. And for logging and such, your host OS should be kept up-to-date. For proper clock-watching by the user, their client machine's OS must also be up-to-date.
Beware: Politicians around the world have shown a penchant for changing their time zones with surprising frequency, and often with little forewarning.
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.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
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. Hibernate 5 & JPA 2.2 support java.time.
Where to obtain the java.time classes?
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With