CODEWITHSUNDEEP
postgresql
Mark Lester
Mark Lester

Reputation: 373

Can I have times greater than 24 hours in postrgres

I am doing timetabling stuff. In railway timetables, if the service starts one day and ends the next, then the convention is to use a +24 hour clock. So if it gets in at 6 in the morning, it's given as thirty-o'clock from the previous start date. This date can be, and in this case is, in a separate table so can't be combined.

Am I going to have to store it in seconds and convert it back on output to the native export format, which expects a +24 hour clock format, and also on the UI. If it means storing stuff as TimeDate from Thursday 1st jan then I'd rather just use an integer and create a virtual type within my application.

Or is there an easy way to relax this 24:00 max on a time field.

Upvotes: 4

Views: 3271

Answers (3)

Patrick
Patrick

Reputation: 32364

Your best bet is to store such information as an interval. You could use interval minute as well if you are going to record arrival times by the minute. Interval can take on any (reasonable) value, so 30:00 is completely valid to indicate that the arrival is 30 hours after some starting point (either departure or midnight of scheduled departure date, depending on your logic). Since an interval is a built-in data type, you can simply add it to other dates or timestamps and get sensible results at database and UI level.

Upvotes: 3

Adam Gent
Adam Gent

Reputation: 49095

It sounds like you want the interval type.

When speaking of time there are three types of time (all of which have surprising complexity):

  • A specific time. A Timestamp.
  • A Duration of time
  • An Interval of time (the time between two specific times.. similar to above but different).

Postgres in this case uses interval to mean duration (but there is a difference in many time libraries such as Joda... ie a year is not always a year precisely).

Upvotes: 4

TheGrandPackard
TheGrandPackard

Reputation: 621

Mark -

That sounds like it could be a challenge to get used to - I've never heard of that sort of time keeping before! With that said, I would not try and force a square peg in a round hole with the database field. Instead, I would use your application to keep track of how you want to display the time and allow the database to do what does best with timestamps. Timestamps are typically stored in epoch form (number of seconds), so store your times as normal and create a wrapper/helper function to display your +24 hour clock format to your users.

Upvotes: -1

Related Questions