Mixing
how to convert date to seconds in java
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?
java postgresql
edited Nov 20 '18 at 17:42
Ishaan Javali
1,2622619
asked Nov 20 '18 at 16:49
AbhishekAbhishek
14114
add a comment |
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?
java postgresql
edited Nov 20 '18 at 17:42
Ishaan Javali
1,2622619
asked Nov 20 '18 at 16:49
AbhishekAbhishek
14114
3
Can you post the code that you have tried so far? You should attempt the problem before asking for a solution.
– Ishaan Javali
Nov 20 '18 at 16:50
"2018-11-21 22:07:20" is a string ? wich version of java do you use?
– Arnault Le Prévost-Corvellec
Nov 20 '18 at 16:55
1
Do both dates come from the same locale? Did the locale's time zone, or time zone rules change in between the two dates? Was there a leap day, leap second, or a daylight savings turnover in between the two dates? Time diffs are harder than some people will try to tell you.
– mypetlion
Nov 20 '18 at 16:57
Possible duplicate of In Java, how do I get the difference in seconds between 2 dates?
– Ole V.V.
Nov 20 '18 at 17:03
I assume you meantimestamp without time zone(which is a pity if true because then you’ve got an ambiguous point in time). According to PostgrSQL JDBC Driver documentation you can get aLocalDateTimefrom JDBC, which will come handy into a couple of the answers.
– Ole V.V.
Nov 20 '18 at 17:06
add a comment |
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?
java postgresql
edited Nov 20 '18 at 17:42
Ishaan Javali
1,2622619
asked Nov 20 '18 at 16:49
AbhishekAbhishek
14114
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?
java postgresql
java postgresql
edited Nov 20 '18 at 17:42
Ishaan Javali
1,2622619
asked Nov 20 '18 at 16:49
AbhishekAbhishek
14114
edited Nov 20 '18 at 17:42
Ishaan Javali
1,2622619
asked Nov 20 '18 at 16:49
AbhishekAbhishek
14114
edited Nov 20 '18 at 17:42
Ishaan Javali
1,2622619
edited Nov 20 '18 at 17:42
Ishaan Javali
1,2622619
edited Nov 20 '18 at 17:42
Ishaan Javali
1,2622619
1,2622619
asked Nov 20 '18 at 16:49
AbhishekAbhishek
14114
asked Nov 20 '18 at 16:49
AbhishekAbhishek
14114
asked Nov 20 '18 at 16:49
AbhishekAbhishek
14114
14114
3
Can you post the code that you have tried so far? You should attempt the problem before asking for a solution.
– Ishaan Javali
Nov 20 '18 at 16:50
"2018-11-21 22:07:20" is a string ? wich version of java do you use?
– Arnault Le Prévost-Corvellec
Nov 20 '18 at 16:55
1
Do both dates come from the same locale? Did the locale's time zone, or time zone rules change in between the two dates? Was there a leap day, leap second, or a daylight savings turnover in between the two dates? Time diffs are harder than some people will try to tell you.
– mypetlion
Nov 20 '18 at 16:57
Possible duplicate of In Java, how do I get the difference in seconds between 2 dates?
– Ole V.V.
Nov 20 '18 at 17:03
I assume you meantimestamp without time zone(which is a pity if true because then you’ve got an ambiguous point in time). According to PostgrSQL JDBC Driver documentation you can get aLocalDateTimefrom JDBC, which will come handy into a couple of the answers.
– Ole V.V.
Nov 20 '18 at 17:06
add a comment |
3
Can you post the code that you have tried so far? You should attempt the problem before asking for a solution.
– Ishaan Javali
Nov 20 '18 at 16:50
"2018-11-21 22:07:20" is a string ? wich version of java do you use?
– Arnault Le Prévost-Corvellec
Nov 20 '18 at 16:55
1
Do both dates come from the same locale? Did the locale's time zone, or time zone rules change in between the two dates? Was there a leap day, leap second, or a daylight savings turnover in between the two dates? Time diffs are harder than some people will try to tell you.
– mypetlion
Nov 20 '18 at 16:57
Possible duplicate of In Java, how do I get the difference in seconds between 2 dates?
– Ole V.V.
Nov 20 '18 at 17:03
I assume you meantimestamp without time zone(which is a pity if true because then you’ve got an ambiguous point in time). According to PostgrSQL JDBC Driver documentation you can get aLocalDateTimefrom JDBC, which will come handy into a couple of the answers.
– Ole V.V.
Nov 20 '18 at 17:06
3
3
Can you post the code that you have tried so far? You should attempt the problem before asking for a solution.
– Ishaan Javali
Nov 20 '18 at 16:50
Can you post the code that you have tried so far? You should attempt the problem before asking for a solution.
– Ishaan Javali
Nov 20 '18 at 16:50
"2018-11-21 22:07:20" is a string ? wich version of java do you use?
– Arnault Le Prévost-Corvellec
Nov 20 '18 at 16:55
"2018-11-21 22:07:20" is a string ? wich version of java do you use?
– Arnault Le Prévost-Corvellec
Nov 20 '18 at 16:55
1
1
Do both dates come from the same locale? Did the locale's time zone, or time zone rules change in between the two dates? Was there a leap day, leap second, or a daylight savings turnover in between the two dates? Time diffs are harder than some people will try to tell you.
– mypetlion
Nov 20 '18 at 16:57
Do both dates come from the same locale? Did the locale's time zone, or time zone rules change in between the two dates? Was there a leap day, leap second, or a daylight savings turnover in between the two dates? Time diffs are harder than some people will try to tell you.
– mypetlion
Nov 20 '18 at 16:57
Possible duplicate of In Java, how do I get the difference in seconds between 2 dates?
– Ole V.V.
Nov 20 '18 at 17:03
Possible duplicate of In Java, how do I get the difference in seconds between 2 dates?
– Ole V.V.
Nov 20 '18 at 17:03
I assume you mean
timestamp without time zone (which is a pity if true because then you’ve got an ambiguous point in time). According to PostgrSQL JDBC Driver documentation you can get a LocalDateTime from JDBC, which will come handy into a couple of the answers.– Ole V.V.
Nov 20 '18 at 17:06
I assume you mean
timestamp without time zone (which is a pity if true because then you’ve got an ambiguous point in time). According to PostgrSQL JDBC Driver documentation you can get a LocalDateTime from JDBC, which will come handy into a couple of the answers.– Ole V.V.
Nov 20 '18 at 17:06
add a comment |
2 Answers
2
active
oldest
votes
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):
LocalDateTimeis 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.
answered Nov 20 '18 at 21:02
Basil BourqueBasil Bourque
108k26372537
add a comment |
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.
answered Nov 20 '18 at 16:58
MichaelMichael
19.6k83470
1
This assumes that the record in the database is from the same time zone that OP's code is going to be running in.
– mypetlion
Nov 20 '18 at 16:59
Nice answer. I recommend you specify time zone explicitly (even if you just specifyZoneId.systemDefault()).
– Ole V.V.
Nov 20 '18 at 17:00
@mypetlion When neither has a timezone, that is only sensible assumption that one can make.
– Michael
Nov 20 '18 at 17:00
1
Agreed. But I do think it worth noting to OP and future readers what assumptions are being made. If OP has control over what gets recorded in the database, it should be recommended to them that they additionally record the time zone, going forward.
– mypetlion
Nov 20 '18 at 17:03
Thanks, @mypetlion, for at least making the assumption clear and explicit. I agree that this will help readers now and in the future (another sensible assumption might be that the date-time from PostgreSQL is in UTC; that would at least be good practice; but it’s still only a fragile assumption).
– Ole V.V.
Nov 20 '18 at 17:10
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53397757%2fhow-to-convert-date-to-seconds-in-java%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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):
LocalDateTimeis 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.
answered Nov 20 '18 at 21:02
Basil BourqueBasil Bourque
108k26372537
add a comment |
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):
LocalDateTimeis 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.
answered Nov 20 '18 at 21:02
Basil BourqueBasil Bourque
108k26372537
add a comment |
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):
LocalDateTimeis 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.
answered Nov 20 '18 at 21:02
Basil BourqueBasil Bourque
108k26372537
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):
LocalDateTimeis 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.
answered Nov 20 '18 at 21:02
Basil BourqueBasil Bourque
108k26372537
edited Nov 20 '18 at 21:19
answered Nov 20 '18 at 21:02
Basil BourqueBasil Bourque
108k26372537
answered Nov 20 '18 at 21:02
Basil BourqueBasil Bourque
108k26372537
answered Nov 20 '18 at 21:02
Basil BourqueBasil Bourque
108k26372537
108k26372537
add a comment |
add a comment |
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.
answered Nov 20 '18 at 16:58
MichaelMichael
19.6k83470
1
This assumes that the record in the database is from the same time zone that OP's code is going to be running in.
– mypetlion
Nov 20 '18 at 16:59
Nice answer. I recommend you specify time zone explicitly (even if you just specifyZoneId.systemDefault()).
– Ole V.V.
Nov 20 '18 at 17:00
@mypetlion When neither has a timezone, that is only sensible assumption that one can make.
– Michael
Nov 20 '18 at 17:00
1
Agreed. But I do think it worth noting to OP and future readers what assumptions are being made. If OP has control over what gets recorded in the database, it should be recommended to them that they additionally record the time zone, going forward.
– mypetlion
Nov 20 '18 at 17:03
Thanks, @mypetlion, for at least making the assumption clear and explicit. I agree that this will help readers now and in the future (another sensible assumption might be that the date-time from PostgreSQL is in UTC; that would at least be good practice; but it’s still only a fragile assumption).
– Ole V.V.
Nov 20 '18 at 17:10
|
show 1 more comment
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.
answered Nov 20 '18 at 16:58
MichaelMichael
19.6k83470
1
This assumes that the record in the database is from the same time zone that OP's code is going to be running in.
– mypetlion
Nov 20 '18 at 16:59
Nice answer. I recommend you specify time zone explicitly (even if you just specifyZoneId.systemDefault()).
– Ole V.V.
Nov 20 '18 at 17:00
@mypetlion When neither has a timezone, that is only sensible assumption that one can make.
– Michael
Nov 20 '18 at 17:00
1
Agreed. But I do think it worth noting to OP and future readers what assumptions are being made. If OP has control over what gets recorded in the database, it should be recommended to them that they additionally record the time zone, going forward.
– mypetlion
Nov 20 '18 at 17:03
Thanks, @mypetlion, for at least making the assumption clear and explicit. I agree that this will help readers now and in the future (another sensible assumption might be that the date-time from PostgreSQL is in UTC; that would at least be good practice; but it’s still only a fragile assumption).
– Ole V.V.
Nov 20 '18 at 17:10
|
show 1 more comment
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.
answered Nov 20 '18 at 16:58
MichaelMichael
19.6k83470
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.
answered Nov 20 '18 at 16:58
MichaelMichael
19.6k83470
answered Nov 20 '18 at 16:58
MichaelMichael
19.6k83470
answered Nov 20 '18 at 16:58
MichaelMichael
19.6k83470
answered Nov 20 '18 at 16:58
MichaelMichael
19.6k83470
19.6k83470
1
This assumes that the record in the database is from the same time zone that OP's code is going to be running in.
– mypetlion
Nov 20 '18 at 16:59
Nice answer. I recommend you specify time zone explicitly (even if you just specifyZoneId.systemDefault()).
– Ole V.V.
Nov 20 '18 at 17:00
@mypetlion When neither has a timezone, that is only sensible assumption that one can make.
– Michael
Nov 20 '18 at 17:00
1
Agreed. But I do think it worth noting to OP and future readers what assumptions are being made. If OP has control over what gets recorded in the database, it should be recommended to them that they additionally record the time zone, going forward.
– mypetlion
Nov 20 '18 at 17:03
Thanks, @mypetlion, for at least making the assumption clear and explicit. I agree that this will help readers now and in the future (another sensible assumption might be that the date-time from PostgreSQL is in UTC; that would at least be good practice; but it’s still only a fragile assumption).
– Ole V.V.
Nov 20 '18 at 17:10
|
show 1 more comment
1
This assumes that the record in the database is from the same time zone that OP's code is going to be running in.
– mypetlion
Nov 20 '18 at 16:59
Nice answer. I recommend you specify time zone explicitly (even if you just specifyZoneId.systemDefault()).
– Ole V.V.
Nov 20 '18 at 17:00
@mypetlion When neither has a timezone, that is only sensible assumption that one can make.
– Michael
Nov 20 '18 at 17:00
1
Agreed. But I do think it worth noting to OP and future readers what assumptions are being made. If OP has control over what gets recorded in the database, it should be recommended to them that they additionally record the time zone, going forward.
– mypetlion
Nov 20 '18 at 17:03
Thanks, @mypetlion, for at least making the assumption clear and explicit. I agree that this will help readers now and in the future (another sensible assumption might be that the date-time from PostgreSQL is in UTC; that would at least be good practice; but it’s still only a fragile assumption).
– Ole V.V.
Nov 20 '18 at 17:10
1
1
This assumes that the record in the database is from the same time zone that OP's code is going to be running in.
– mypetlion
Nov 20 '18 at 16:59
This assumes that the record in the database is from the same time zone that OP's code is going to be running in.
– mypetlion
Nov 20 '18 at 16:59
Nice answer. I recommend you specify time zone explicitly (even if you just specify
ZoneId.systemDefault()).– Ole V.V.
Nov 20 '18 at 17:00
Nice answer. I recommend you specify time zone explicitly (even if you just specify
ZoneId.systemDefault()).– Ole V.V.
Nov 20 '18 at 17:00
@mypetlion When neither has a timezone, that is only sensible assumption that one can make.
– Michael
Nov 20 '18 at 17:00
@mypetlion When neither has a timezone, that is only sensible assumption that one can make.
– Michael
Nov 20 '18 at 17:00
1
1
Agreed. But I do think it worth noting to OP and future readers what assumptions are being made. If OP has control over what gets recorded in the database, it should be recommended to them that they additionally record the time zone, going forward.
– mypetlion
Nov 20 '18 at 17:03
Agreed. But I do think it worth noting to OP and future readers what assumptions are being made. If OP has control over what gets recorded in the database, it should be recommended to them that they additionally record the time zone, going forward.
– mypetlion
Nov 20 '18 at 17:03
Thanks, @mypetlion, for at least making the assumption clear and explicit. I agree that this will help readers now and in the future (another sensible assumption might be that the date-time from PostgreSQL is in UTC; that would at least be good practice; but it’s still only a fragile assumption).
– Ole V.V.
Nov 20 '18 at 17:10
Thanks, @mypetlion, for at least making the assumption clear and explicit. I agree that this will help readers now and in the future (another sensible assumption might be that the date-time from PostgreSQL is in UTC; that would at least be good practice; but it’s still only a fragile assumption).
– Ole V.V.
Nov 20 '18 at 17:10
|
show 1 more comment
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53397757%2fhow-to-convert-date-to-seconds-in-java%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Can you post the code that you have tried so far? You should attempt the problem before asking for a solution.
– Ishaan Javali
Nov 20 '18 at 16:50
"2018-11-21 22:07:20" is a string ? wich version of java do you use?
– Arnault Le Prévost-Corvellec
Nov 20 '18 at 16:55
1
Do both dates come from the same locale? Did the locale's time zone, or time zone rules change in between the two dates? Was there a leap day, leap second, or a daylight savings turnover in between the two dates? Time diffs are harder than some people will try to tell you.
– mypetlion
Nov 20 '18 at 16:57
Possible duplicate of In Java, how do I get the difference in seconds between 2 dates?
– Ole V.V.
Nov 20 '18 at 17:03
I assume you mean
timestamp without time zone(which is a pity if true because then you’ve got an ambiguous point in time). According to PostgrSQL JDBC Driver documentation you can get aLocalDateTimefrom JDBC, which will come handy into a couple of the answers.– Ole V.V.
Nov 20 '18 at 17:06