1

I am defining my data at this time, in a Java application using a MySQL database. One of the kinds of data that I need is a date/time period. I need to store additive amounts in date-time.

I am otherwise planning to use Java LocalDateTime and MySQL's TIMESTAMP data type for storing date/times. I plan to convert LocalDateTime using the Java Timestamp functions.

Can I store a period in a MySQL TIMESTAMP? How would it handle "1 day, 3 hours, and 6 minutes" as a TIMESTAMP (without being in an epoch)? If not, what would work well with the Java approach I am using? TIA.

DETAILS edit-added: This is for local use, in one time zone per distribution. The Java app on PCs is where LocalDateTime will be captured and viewed via my Java GUI, where the needed granularity will be no finer than minutes. Then, they get stored in the DB as TIMESTAMP.

The periods will be stored in a "shift template" table in the DB, as start and end time periods relative to the zero-point of the template, for each shift in one cycle of shifts. I will calculate all "Shift" records (in Java) from a given start date-time, to some number of months in advance, by looped processing of these shift template records.

Example of use: This sample template will define 12-hour shifts, day and night, for 8 days, for 4 crews, with each getting 4 days on and then 4 days off (16 records in this template table). All values are add-amounts to the caller's zero point. Each preset template record contains:

  • add-amount for shift number
  • time-date add-amount for start of shift
  • time-date add-amount for end of shift
  • a crew identifier for the shift

Grid Template (records)
(Time periods are shown as DD:hh:mm)
Shift 01: 00:00:00 – 00:12:00, crew A
Shift 02: 00:12:00 – 01:00:00, crew B
Shift 03: 01:00:00 – 01:12:00, crew A
…
Shift 09: 04:00:00 – 04:12:00, crew C
Shift 10: 04:12:00 – 05:00:00, crew D
… (up to shift 16)

Next are the shift records I would like to produce from the above template record set, using 06/01/2016 06:00 as an example starting date-time (or zero point), with:

  • Shift number
  • Shift starting date-time
  • Shift ending date-time
  • Crew identifier for the shift

Shift (records)
(Date-times are shown as MM/DD/YYYY hh:mm)
Shift 01: 06/01/2016 06:00 – 06/01/2016 18:00, crew A
Shift 02: 06/01/2016 18:00 – 06/02/2016 06:00, crew B
Shift 03: 06/02/2016 06:00 – 06/02/2016 18:00, crew A
…
Shift 09: 06/05/2016 06:00 – 06/05/2016 18:00, crew C
Shift 10: 06/05/2016 18:00 – 06/06/2016 06:00, crew D
…
Shift 17: 06/09/2016 06:00 – 06/09/2016 18:00, crew A
Shift 18: 06/09/2016 18:00 – 06/10/2016 06:00, crew B
…
Shift 25: 06/13/2016 06:00 – 06/13/2016 18:00, crew C
Shift 26: 06/13/2016 18:00 – 06/14/2016 06:00, crew D
…

Edited throughout, from "offset" to "period", with some clarification

Improve this question
6
  • 1
    To me, it would seem most intuitive to store time offsets as integers. The value would then correspond to the number of minutes/seconds/millis (depending on the granularity you need). If you need to present the offset in other time units, the conversions are simple arithmetic. Commented May 29, 2016 at 18:16
  • @MickMnemonic The Java code will define the granularity. How would you get/put the integers from in Java? Commented May 29, 2016 at 18:33
  • 1
    It's hard to answer without knowing more about your application, but conversions in Java are simple through the TimeUnit enum. Commented May 29, 2016 at 18:45
  • @MickMnemonic If you coded a simple example of using LocalDateTime and TimeUnit for converting to and from int, where only as fine as minutes are preserved, I would be happy to accept that as an answer. Commented May 29, 2016 at 18:56
  • Could you open up the domain model and use cases a bit? Are you getting timestamps or time offsets as inputs, i.e. what needs to be calculated before the data is stored in the DB? Commented May 29, 2016 at 19:51

1 Answer 1

Reset to default
1

‘Offset’ is wrong term

In date-time work, the term ‘offset’ usually refers to offset-from-UTC. An offset is a number hours, minutes, and seconds ahead (east) or behind (west) of the UTC line. A time zone is a set of rules for handling adjustments to an offset to handle anomalies such as Daylight Saving Time (DST).

The term you need is ‘elapsed’ to track the amount of time that occurred between some start-and-stop moments.

Interval

To track the original two moments from the start and the stop, either (a) create your own interval class storing a pair of Instant or ZonedDateTime objects or (b) use the Interval class in the ThreeTen-Extra project that extends the java.time classes (310 being the number of the JSR defining java.time).

Duration

To track a span of time unattached to the timeline, use the Duration class. See the Oracle Tutorial on Period and Duration. A duration tracks a total number of seconds plus a fraction of a second in nanoseconds.

Instant start = Instant.now();
Thread.sleep( 5000 ); // Milliseconds of sleep.
Instant stop = Instant.now();

Duration duration = Duration.between( start , stop );

The java.time classes use the ISO 8601 standard formats by default for parsing and generating Strings as textual representations of their date-time values. For a duration, the standard uses the format like P1DT3H6M for “1 day, 3 hours, and 6 minutes”. The P marks the beginning (period) and the T separates days from the hours-minutes-seconds. You could store these strings as they can be easily handled by java.time. But they will not naturally sort of course.

If you need to sort or query on the length of the duration, I suggest writing an integer in the database for the total number of minutes (your stated granularity) in the duration. You can obtain that total number by calling Duration::toMinutes().

Since you care about specific dates, and specific spans of time, you should not be using the Local… types. The Local… types specifically lose time zone information. Without time zone info you will not be able to handle Daylight Saving Time (DST) and other anomalies. With Local… types, all days are assumed to be 24 hours long, which is not true in real life in various time zones. Instead use ZonedDateTime type in Java, and a database type close to the SQL standard’s TIMESTAMP WITH TIME ZONE

The rest of the Question eludes me, losing me with your talk of “shift-template” and “zero-point”. But my gut tells me that you are working too hard due to not understanding effective ways to handle date-time data. Date-time handling is tricky. I suggest doing more study, such as searching and reading "java date" related Questions on Stack Overflow.

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

2 Comments

I thank you for the time you have given to this, Basil. I have re-edited "offset" to "period" throughout, with some other clarifications in response to what you are saying. It's true - I have to learn more, and Period and Duration look like where I need to focus. After that, it will just be as to how I store/retrieve those periods in the database.
@MarkMeyers More about terms… While 'offset' is commonly used in a consistent manner, other date-time terms are not. In particular 'period', 'duration', 'interval', and other span-of-time terms have no commonly agreed usage. One example of contradictory use: What the ISO 8601 spec apparently calls "Duration" has a format starting with a P which I assume come from the word period. In java.time, the Period class is for years-month-days while Duration is for days-hours-minutes-seconds. I once read of an attempt to officially specify date-time terminology, but found no follow-up.

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.