Postgres timestamp in java

Formatting java.time Instant values from PostgreSQL Timestamp values-postgresql

Short answer:
It’s because the java.time classes are separating the concepts «point in time» and «time as a human sees it» whereas Timestamp / Date don’t.

Long answer:
You are right, an Instant is representing a single point in the time line. That’s why it is not possible to give a correct/unique answer to the question «what’s the year/day/time?». It depends on where on the world the question is asked: In New York it differs from Sidney.
But your DateTimeFormatter is asking exactly this question. And that is why you get an UnsupportedTemporalTypeException .

Date and its subclass Timestamp on the other hand are mixing up the two concepts. While internally storing a long «point in time», they «answer» if asked for their year. Usually they are assuming the local time zone of the system to pin the the long -offset to an specific time zone.
This is error-prone and let to introduction of Calendar , JodaTime and java.time.

Now, why is your DateTimeFormatter not smart enough to align Instant to the default TimeZone?
DateTimeFormatter works on the TemporalAccessor interface and does not differ between concrete implementations like Instant , LocalDateTime or ZonedDateTime . There are legitimate formatting cases for all of those implementations and I assume it is simply not feasible to check the concrete object’s compatibility with the given format let alone to perform correct conversions.

The solution: You have to align your timestamp to a timezone/offset yourself:

System.out.println(timestampFormatter.format( fooRecord.getBar().toLocalDateTime().atZone(ZoneId.systemDefault())); 

More questions

• Formatting java.time Instant values from PostgreSQL Timestamp values
• How to convert PostgreSQL timestamp with time zone to Java Instant or OffSetDateTime?
• PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone
• How to get the date and time from timestamp in PostgreSQL select query?
• Load NULL TIMESTAMP with TIME ZONE using COPY FROM in PostgreSQL
• Create a timestamp with time zone in PostgreSQL from Liquibase XML
• Change column type from character varying to timestamp without time zone in PostgreSQL
• How to convert time interval from PostgreSQL in java
• Retain time zone information from postgresql timestamp in go
• Postgresql Date from Timestamp to String in Java
• PostgreSQL 9.3: Check only time from timestamp
• Date time is wrong when retrieving timestamp from PostgreSQL database
• Inserting values from java GUI into postgresql data base
• Can you recover the full timezone name from a Postgresql TIMESTAMP WITH TIME ZONE field?
• How to print Timestamp values in Java from postgres db?
• How do you get the values from a stored procedure in postgresql into a java application? I keep getting an error
• Get maximum, minimum timestamp values from table in postgresql
• Convert Timestamp from PostgreSQL to Time Zone in Query (Rails)
• PostgreSQL SQLalchemy and timestamp with time zone, why is incognito different from normal?
• Map values from one table to another table based on timestamp in postgresQL
• Why date time different 1 hour when retrieve timestamp data of postgresql from rails
• Select values from a table if the table name has timestamp in postgreSQL from R
• count difference in seconds between values from table and some moment in time using PostgreSQL
• PostgreSQL select a fixed time intervals from a range of days defined in timestamp
• How to obtain and then compare two timestamp values from postgreSQL via python?
• Calculating a Week Ending Date from a date in a Postgresql database — ERROR: operator does not exist: timestamp without time zone + double precision
• how to get the time from timestamp without time zone data type of parameter in postgreSQL
• Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
• PostgreSQL alter type timestamp without time zone -> with time zone
• How to write a postgresql query for getting only the date part of timestamp field, from a table

More questions with similar tag

• PGPool II + Django w/ psycopg2 load balancing
• Time zone issue with Django
• In clause with a list of declared variables PostgreSQL
• can’t btree index function that returns text
• How to group by period of time?
• postgres cannot create extension postgis
• Update query on jsonb column only inserts first value
• Mapping a Many-to-Many Releationship Using Doobie
• Springboot | JSONB Postgres | Exception: Unable to load class [jsonb]
• ERROR: type «json_param» does not exist at character 252
• How to insert things like «now() -interval ‘2 minutes'» into PHP PDO query?
• Problems with postgres and Unicorn server
• Is it necessary the express.js framework to develop nodeJS web application
• Updating with INSERT INTO on Conflict Error
• How can I query concurrent events, i.e. usage, in postgres?
• Rails — order by hour
• QueryDSL & PostgreSQL enums?
• Is a query with intersect equivalent to those ‘where a and b’?
• Storing and querying a sequence in python
• Why is the Postgres «mode» function so different from «avg», «max», and other aggregates?
• Postgres showing 1 week before
• How to extract the values from json input in postgresql?
• Return 1 specific value to all row using Window Function and Filter
• I want to create a Table from the data fetched from a View
• Error Creating Sequence In PostgreSQL
• Schema for golf tournament scores
• Is it safe to change Collation on Postgres (keeping encoding)?
• Improving performance of a select query
• How to return updated rows from function
• Updating PostgreSQL database from R shiny app

Источник

Postgres timestamp in java

March 01, 2015 / by Jörg Rathlev / Software engineer / @

Using the new java.time API with PostgreSQL

This article gives you a short overview of how to use Java 8’s new date and time classes with JDBC. Example code is included for storing and retrieving timestamps in a relational database.

If you’re a Java developer, you probably already know that Java 8 ships with a new date and time API. There is also a backport available for Java 6 and 7. One of the specification leads for the new date and time API was Stephen Colebourne, who previously designed the Joda Time library for Java.

Lots of articles have already been published that explain the benefits of using the new date and time API, so I won’t repeat them here. In practice, the main drawback is probably that you cannot use the new API everywhere simply because you have to interface with libraries that still use the date and time classes from earlier versions of Java. One of those libraries, and the subject of this blog post, is JDBC.

JDBC 4.2

Java 8 includes JDBC version 4.2. In this version of JDBC, the familiar classes java.sql.Timestamp , java.sql.Date , and java.sql.Time have been extended with new methods to convert to and from the new date and time types of java.time . For example, an SQL timestamp value can be converted from an instant value by using the static factory method Timestamp#from(Instant) , and converted to an instant by using the method toInstant() .

If you’re using a JDBC 4.2-compliant driver, the driver should automatically map the new types java.time.LocalDateTime , java.time.OffsetDateTime , and so on to their respective types in SQL. However, at the time of writing, the JDBC drivers at least for PostgreSQL and MySQL do not yet support JDBC 4.2.

Example: Storing timestamps in a database

Let’s now look at a simple example: storing timestamps in the database. The simplest way to create a timestamp in Java with the new date and time API is to use the new Instant type:

Instant eventOccurred = Instant.now(); 

To store this value in a database, convert it to a java.sql.Timestamp first. This example also uses a Calendar object to explicitly store the timestamp in UTC time. This is important if you use a database that stores time zones along with the values and want that time zone to be UTC, or if you’re using the SQL TIMESTAMP (without time zone) type and want to ensure that the timestamp is stored in UTC time instead of in the JVM’s time zone.

Calendar utc = Calendar.getInstance(TimeZone.getTimeZone("UTC")); PreparedStatement ps = . ps.setTimestamp(1, Timestamp.from(eventOccurred), utc); 

Although dealing with time zones is not directly related to the new date and time APIs, using the new classes can actually improve the readability of the above code. With a JDBC driver that supports version 4.2 of the JDBC specification, you should instead be able to use the following code, which does not require a Calendar object (note that I have not tested this):

PreparedStatement ps = . ps.setObject(1, eventOccurred.atOffset(ZoneOffset.UTC)); 

When reading from the database, use the new toInstant method of Timestamp to convert the value back into an Instant . You can then use the new date and time API for the actual application logic:

ResultSet rs = . Instant eventOccurred = rs.getTimestamp('column_name', utc).toInstant(); // for example: Duration d = Duration.between(eventOccurred, Instant.now()); System.out.println("Stored event occurred " + d.toMillis() + " milliseconds ago."); 

Summary

Java 8’s new date and time API is huge improvement over the date and time classes previously included in the JDK. The old date and time classes have been extended with sensible methods to convert to and from the new types. Until JDBC drivers support the new date and time types directly, explicit conversion is still required.

Источник