0

I have a field in postgres

Column     |            Type          
created_at    | timestamp without time zone

I have a unix timestamp stored in long in Java

long createdAtTime = data.getcreatedAtTime();

I want to convert it to timestamp in java so that I can store with activejdbc into postgres

I tried the following

Date convertedTime = new Date(createdAtTime*1000L);
record.set("created_at", convertedTime);
record.saveIt();

But I get the following error:

Can't infer the SQL type to use for an instance of java.util.Date. Use setObject() with an explicit Types value to specify the type to use.

Should I be using a different way to convert the date first?

  • 3
    You need to use a `java.sql.Timestamp` not a `java.util.Date`. – Boris the Spider Dec 17 '14 at 17:58
  • possible duplicate of [What is the most recommended way to store time in PostgreSQL using Java?](http://stackoverflow.com/questions/6627289/what-is-the-most-recommended-way-to-store-time-in-postgresql-using-java). And [this one](http://stackoverflow.com/questions/18132219/saving-timestamps-in-postgres-based-on-java-dates). And others. – Basil Bourque Dec 17 '14 at 20:38
  • BTW, you should be using TIMESTAMP WITH TIME ZONE, not WITHOUT. Per [post by David E Wheeler](http://justatheory.com/computers/databases/postgresql/use-timestamptz.html) (Postgres expert). – Basil Bourque Dec 17 '14 at 20:40

3 Answers3

0
java.sql.Timestamp timestamp = new Timestamp(createdAtTime*1000L); 
record.set("created_at", convertedTime);
record.saveIt();

Instead of util you have tried with java.sql.Date. Its a native type of SQL.

Siva Kumar
  • 1,983
  • 3
  • 14
  • 26
  • 2
    This answer is incorrect. A [java.sql.Date](http://docs.oracle.com/javase/8/docs/api/java/sql/Date.html) is intended for a date-only value without time-of-day. Not what the Question demands. – Basil Bourque Dec 17 '14 at 20:36
  • `java.sql.Date` will truncate the time. – Boris the Spider Dec 17 '14 at 21:19
  • @BoristheSpider we can use for that java.sql.Timestamp – Siva Kumar Dec 18 '14 at 00:51
  • @BasilBourque How can this is wrong answer. We can use java.sql.Date or java.sql.TimeStamp for dates in database – Siva Kumar Dec 18 '14 at 00:53
  • 1
    @SivaKumar A java.sql.Date sets its internal time to 00:00:00 as a hack to pretend to be a date-only without a time-of-day. The user's question is for tracking moment when data is created. **Your answer loses data**, namely the time-of-day portion of the Timestamp in the database. I would call an answer that needlessly loses data to be incorrect. Please correct me if I'm misunderstanding something. – Basil Bourque Dec 18 '14 at 01:22
  • @BasilBourque Yes. But in SQL We can use java.sql.Date instead of java.util.Date. sql date only contain date-only. So We can use java.sql.TimeStamp for java.util.Date. java.util.Date can be used other sql purpose. – Siva Kumar Dec 18 '14 at 10:56
  • @SivaKumar No, `java.sql.Date` *pretends* to represent a date-only value without a time-of-day and without a time zone, while in fact it contains both. By inheriting from `java.util.Date` (a *horrible* design flaw), this class is actually a count of milliseconds from 1970-01-01T00:00Z *plus* a fraction of a second in nanoseconds. This leads to all kinds of confusion and trouble. **Never use `java.sql.Date`** — now replaced by `java.time.LocalDate`. Furthermore, as I first commented, this Answer fail to address the Question which asks about moments not date-only. – Basil Bourque Oct 14 '18 at 22:41
0

Incompatible types

I have a field in postgres …

timestamp without time zone …

…and…

I have a unix timestamp stored in long in Java

long createdAtTime = data.getcreatedAtTime();

This is a contradiction.

The SQL-standard type TIMESTAMP WITHOUT TIME ZONE purposely lacks any indicator of time zone or offset-from-UTC. As such, this type does not represent a moment, is not a point on the timeline. This type represents potential moments along a range of about 26-27 hours, the range of time zones around the globe.

If you are trying to track specific moments, use the other SQL-standard type, TIMESTAMP WITH TIME ZONE. In Postgres, all values of this type are stored in UTC (an offset of zero). If you pass a value indicating some other offset or time zone, Postgres adjusts the value to UTC before storing.

When retrieving a value from a column of type TIMESTAMP WITH TIME ZONE in Postgres, you are always getting a value in UTC. Unfortunately, some well-intentioned tools or drivers sitting between you and the database may decide the dynamically apply a time zone to the value. While well-intentioned, I consider this quite the anti-feature. This behavior creates the illusion of a time zone stored in the database while in fact Postgres only stores UTC in this type.

Date convertedTime = new Date(createdAtTime*1000L);

The java.util.Date class is terrible, poorly designed and flawed. Never use this class nor its siblings, Calendar, SimpleDateFormat, and such. These are all legacy now, supplanted years ago by the modern java.time classes defined in JSR 310.

Instant

The Instant class takes over for java.util.Date. Both classes represent a moment in UTC, though Instant has a finer resolution of nanoseconds versus milliseconds.

unix timestamp stored in long in Java

If you have a count of whole seconds from the epoch reference of the first moment of 1970 in UTC, 1970-01-01T00:00:00Z, convert to an Instant.

Instant instant = Instant.ofEpochSecond( 1_539_555_140L ) ;

Tip: Do not make a habit of tracking time as a count-from-epoch. This is ambiguous (different systems use different resolutions and different epoch references), error-prone, and makes debugging/logging treacherous. Use java.time objects and standard ISO 8601 strings for representing date-time values.

Your JDBC driver may be able to accept an Instant.

myPreparedStatement.setObject( … , instant ) ;

Retrieval:

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

OffsetDateTime

If not supporting Instant, use convert to OffsetDateTime. Any JDBC 4.2 or later driver is required to support OffsetDateTime.

OffsetDateTime represents a date and time-of-day with an offset-from-UTC. In contrast, Instant is fixed at UTC, serving as a basic building-block class in java.time framework. Also, OffsetDateTime is more flexible with abilities such as generating strings in various formats versus Instant using only standard ISO 8601 format.

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;
myPreparedStatement.setObject( … , odt ) ;

Retrieval:

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

…or…

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

LocalDateTime

If you are not trying to represent moments, such as database type TIMESTAMP WITHOUT TIME ZONE, use the LocalDateTime class.

But if you are thinking use of these types is somehow avoiding the work of using time zones in tracking moments, you are sorely mistaken. This is a “pay now or pay later” situation: Either learn basic date-time concepts and handling practice now, or desperately wrestle with a horrible mess of failed data later.

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.

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
0

I see that your Database column stores value as time stamp without time zone, why don't you try this

Timestamp current = Timestamp.from(Instant.now());
record.set("created_at", current);//I don't know if you might need to parse
record.saveIt();
Sukhinderpal Mann
  • 744
  • 1
  • 10
  • 23