1

I want to store time interval configuration into database like this:

12 hours -> 43200
24 hours -> 86400
42 hours -> 172800
72 hours -> 259200

But I'm not aware how to convert for example the value 86400 into LocalDateTime because I want to use this value in order to calculate time interval for SQL query.

What is the proper way to convert this value into LocaLDateTime?

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
Peter Penzov
  • 1,126
  • 134
  • 430
  • 808
  • Hi check if your answer is not here: https://stackoverflow.com/questions/25458832/how-can-i-convert-an-integer-e-g-19000101-to-java-util-date Have a good day ! – Dino Grrr Nov 17 '19 at 10:49
  • What is wrong with a simple `12 * 60 * 60` calculation? – Nicholas K Nov 17 '19 at 10:51
  • 1
    Are you sure you need a `LocalDateTime` and not a [period or duration](https://docs.oracle.com/javase/tutorial/datetime/iso/period.html) ? – Abra Nov 17 '19 at 10:52
  • @NicholasK can you show me code example please? – Peter Penzov Nov 17 '19 at 10:54
  • @Abra yes I need LocalDateTime. Can you show me example please? – Peter Penzov Nov 17 '19 at 10:54
  • Unless, I've missed something if you get the value `86400` and you want to convert that into hours you can do `86400 / 60 / 60` – Nicholas K Nov 17 '19 at 10:57
  • 4
    As far as I know, 12 hours is a [duration](https://docs.oracle.com/javase/8/docs/api/java/time/Duration.html) and not a date-time value. – Abra Nov 17 '19 at 10:59

1 Answers1

2

I suggest: In your SQL database use either a proper datatype for an amount of time, a duration, or a char holding a duration in ISO 8601 format. In Java use Duration (never LocalDateTIme).

If your database has a datatype for a duration, consider using is, though do weigh the pros and cons. It probably means that you will have to transfer duration values to and from your database as text strings, which is not ideal. Some RDBMSs claim that their time datatype can be used both for a time of day and for a duration, a hack, but if your database is happy to display 72 hours as 72:00 rather than 259200, that’s definitely an advantage.

If you cannot or don’t want to use a proper datatype of your database, my second suggestion is ISO 8601 strings. The ISO 8601 format for a duration goes like PT12H (for 12 hours; read as a period of time of 12 hours). 72 hours can be given as P3D or PT72H (assuming that we can safely convert 72 hours into 3 days; this is not always correct). While this format looks a bit weird, I still find it more readable than 259200. varchar(13) or so should suffice for your purpose.

In Java use a Duration. If the duration comes out of your database as 72:00, search for how to parse that into a Duration, and search again for how to format it back. If it comes as PT12H, the Duration class parses that directly:

    String durationStringFromDb = "PT12H";
    Duration dur = Duration.parse(durationStringFromDb);
    System.out.println(dur);

Output:

PT12H

The Duration prints back in ISO 8601 format, its toString method generates that. If you need the duration as seconds for some purpose:

    System.out.println(dur.getSeconds());

43200

Edit: Or the other way, if you’ve got a number of seconds from somewhere:

        int numberOfSeconds = 172800;
        Duration dur = Duration.ofSeconds(numberOfSeconds);
        System.out.println(dur);

PT48H

Links

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
  • Thanks. I want to use Duration in order to get some time interval in seconds. Can you show me if I have 172800 seconds how to convert it into Java Duration? – Peter Penzov Nov 17 '19 at 14:32
  • That’s just as trivial, @PeterPenzov. Did you try [the documentation](https://docs.oracle.com/javase/10/docs/api/java/time/Duration.html)? In any case I have edited. – Ole V.V. Nov 17 '19 at 15:29