CODEWITHSUNDEEP
javamysqldate
Mark Meyers
Mark Meyers

Reputation: 563

Using TIMESTAMP (?) for date-time durations with Java and MySQL

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:

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 (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

Upvotes: 1

Views: 457

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 338181

‘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.

Upvotes: 1

Related Questions