0

I have postgresql time column with type TIMESTAMP WITH TIME ZONE having a sample value like 1970-01-01 00:00:00+05:30 I want to compare a value with Date value like: 

Date date = new Date(0L);

i.e. Tue Jan 19 13:55:24 IST 2016

So I used 

Timestamp timeStampDate = new Timestamp(date.getTime());

The above code gives timeStampDate.getTime() as 0.

How to convert it in the above format so that it can be compared, Currently comparing is giving no results. How to format this date.

I basically want to convert " Date is Thu Jan 01 05:30:00 IST 1970 " to this format : "1970-01-01 00:00:00+05:30"

Improve this question
5
  • The code looks like Java - there is no SQL involved here. Do you want a Java solution or a SQL solution? Commented Jan 19, 2016 at 8:31
  • I just need a java solution . I m using SpringDataJpa to query with database later. Commented Jan 19, 2016 at 8:32
  • Show what the PostgreSQL value looks like in Java. How do you fetch the data? Do you store the DB value in a String? Commented Jan 19, 2016 at 9:58
  • In this kind of Question, you should be very explicit about java.util.Date versus java.sql.Date/.Timestamp. Commented Jan 21, 2016 at 1:22
  • Why do you want to compare some Date object to a Date(0L) object? Your Question is not clear. Commented Jan 21, 2016 at 1:26

1 Answer 1

Reset to default
1

java.time

If your database driver supports JDBC 4.2 or later, use Instant to get data from your database where stored in a TIMESTAMP WITH TIME ZONE column.

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

For an older JDBC driver, use java.sql.Timestamp to get data from your database by calling getTimestamp on your ResultSet. That Timestamp object is in UTC. Postgres stores your TIMESTAMP WITH TIME ZONE in UTC, by the way, and loses the original time zone or offset-from-UTC that came with the incoming data after using that data to adjust into UTC as part of the storage process.

You should convert your java.sql.Timestamp/.Date/.Time to java.time types as soon as possible. Then proceed with your business logic.

In a nutshell, java.time is… An Instant is a moment on the timeline in UTC. Apply a time zone (ZoneId) to get a ZonedDateTime.

Convert To java.time

So convert from Timestamp, get an Instant, apply a ZoneId.

java.sql.Timestamp ts = myResultSet.getTimestamp( 1 );
…
Instant instant = ts.toInstant();

Specify your desired/expected time zone. Apply to the Instant.

ZoneId zoneId = ZoneId.of( "Asia/Kolkata" );
ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );

Comparison

Get the current moment, for use in comparison.

ZonedDateTime now = ZonedDateTime.now( zoneId );

Compare by calling the isEqual, isBefore, or isAfter methods.

Boolean beforeNow = zdt.isBefore( now );

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.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.