how to convert date to seconds in java

Question

I have a date column in postgres db whose value is 2018-11-20 22:07:20. The datatype is timestamz. I want to get the above value in java code and convert that to seconds with respect to the current time. Suppose the current date is 2018-11-21 22:07:20 then the final answer should be 86400 seconds. Can anyone help me with this?

Answer 1

tl;dr

Duration                                                // Represent a span-of-time unattached to the timeline.
.between(                                               // Calculate elapsed time between two moments.
    OffsetDateTime.now( ZoneOffset.UTC ) ,              // Capture the current moment as seen in UTC.
    myResultSet.getObject( … , OffsetDateTime.class )   // Retrieve the moment stored in your database as a `OffsetDateTime` object, *not* as a mere string.
)                                                       // Return a `Duration` object.
.toSeconds()                                            // View that duration as a total number of whole seconds. Obviously, any fractional second is ignored.

Details

The Answer by Michael is close but not quite correct.

• It fails to account for anomalies in your local time zone, such as Daylight Saving Time (DST): LocalDateTime is the wrong class there, as it cannot, by definition, represent a moment.
• It also fails to address the bigger problem that dumb strings are being used to exchange date-time values with the database rather than using smart objects.

date column in postgres db whose value is 2018-11-20 22:07:20

No, that is not the value of the column. That is a textual representation of the value. What is the distinction? Well, unfortunately, many tools used to access your data take the liberty of altering the data being retrieved by applying a time zone adjustment.

Even worse, your example text lacks an indicator of time zone or offset-from-UTC. This contradicts your next statement.

The datatype is timestamz.

I think you misspelled timestampz (missing the p). Even so, this seems to be incorrect, as no such type is listed among the Postgres date/time types. Some systems use that word as an abbreviation, but I recommend always using the longer SQL-standard name for clarity.

You likely meant the type TIMESTAMP WITH TIME ZONE which Postgres, like some other databases, stores as a value in UTC. Any indicator of time zone or offset-from-UTC present within incoming data is used to adjust to UTC, then the indicator is discarded. So values going into, and out of, a TIMESTAMP WITH TIME ZONE column in Postgres is always in UTC. Beware, as mentioned above, some tools interfere with the data retrieval by injecting a time zone adjustment, a well-intentioned though very confusing anti-feature.

Smart objects, not dumb strings

As of JDBC 4.2, we can exchange java.time objects with the database via setObject and getObject methods. Use the object rather than mere strings to exchange date-time values.

OffsetDateTime

Retrieve your value from a column of type TIMESTAMP WITH TIME ZONE as an OffsetDateTime value with its offset set to UTC.

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

For comparison, get the current moment in UTC. Specify the offset using the constant ZoneOffset.UTC.

OffsetDateTime odtNow = OffsetDateTime.now( ZoneOffset.UTC ) ;

To generate text representing that duration in standard ISO 8601 format, call OffsetDateTime::toString.

Duration

Capture elapsed time as a Duration object.

Duration d = Duration.between( odtNow , odtThen ) ; 

To generate text representing that duration in standard ISO 8601 format, call Duration::toString.

String output = d.toString() ;  // PnYnMnDTnHnMnS

To see that entire duration as one big count of whole seconds, call Duration::toSeconds.

long secondsElapsed = d.toSeconds() ;

ZonedDateTime

By the way, if you wish to view either the that odtThen or odtNow value through the lens of the wall-clock time used by the people of a particular region (a time zone), apply a ZoneId 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 2-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( "Pacific/Auckland" ) ;
ZonedDateTime zdt = odtThen.atZoneSameInstant( z ) ;

---

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?

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

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.

Answer 2

Parse into a LocalDateTime, then get the Duration between that and the current time, and convert it to seconds.

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("your pattern");
LocalDateTime dateTime = LocalDateTime.parse("2018-11-20 22:07:20", formatter);
return Duration.between(dateTime, LocalDateTime.now()).getSeconds();

You can work out the pattern yourself.