0

I have a Database in 4th Dimension. It stores its time values in number format (seconds).

Now I have a Time duration of 29:30:00 ( 29 hours: 30 min : 00 Sec )

But this conflicts with the TIME rules of mySQL ( cant be > 23:59:59 )

I tried to convert this to number and save it, but I suspect it MySQL sees that it is a Time field and a number is not valid.

I was thinking about a format like DD:HH:MM:SS but im not sure if that is allowed.

Noctis Skytower
  • 21,433
  • 16
  • 79
  • 117
morne
  • 4,035
  • 9
  • 50
  • 96
  • 1
    [Not exactly](https://dev.mysql.com/doc/refman/5.6/en/time.html): "the range for TIME values is '-838:59:59.000000' to '838:59:59.000000'" – Álvaro González May 22 '17 at 13:58
  • 1
    What don't you also store your durations in plain seconds, and then convert to hours/minutes/seconds format when you need it in the presentation layer? – Tim Biegeleisen May 22 '17 at 13:59
  • @TimBiegeleisen, Yeah, that might be a good alternative. – morne May 22 '17 at 14:00
  • @ÁlvaroGonzález. Okay you probably right, but 4D still gives an error it I save a time above the normal 24h – morne May 22 '17 at 14:01
  • @morne AFAIK most databases store dates/times in terms of seconds since the epoch internally. – Tim Biegeleisen May 22 '17 at 14:01
  • I can't help with with that product (it's the first time I ever hear about it). Is there some code you could share? What's the *exact* error message? – Álvaro González May 22 '17 at 14:02
  • @morne in [4D](http://livedoc.4d.com/4Dv15.4/help/Title/en/page262.html#12733) "A Time field, variable or expression can be in the range of 00:00:00 to 596,000:00:00" and i just tested 29:30:00 to confirm [it works](https://i.stack.imgur.com/4phqI.png) – Tim Penner May 23 '17 at 15:43
  • you can save the day as INT in other column – CSK May 23 '17 at 15:53
  • @morne do you actually get an error message or are you just concerned that the value greater than 24 hours does not meet the database engine rules? As mentioned in my [answer](https://stackoverflow.com/a/44139544/5971390), both the [4D](http://livedoc.4d.com/4Dv15.4/help/Title/en/page262.html#12733) and [mySQL](https://dev.mysql.com/doc/refman/5.6/en/time.html) database engines allow greater than 24 hours. Can you provide an [mcve] that demonstrates the actual issue? – Tim Penner May 23 '17 at 23:05
  • @TimPenner I just get a "General Error". I have not looked at it since, but will let you know. – morne May 25 '17 at 10:21

1 Answers1

0

There should be no problem storing time values greater than 24 hours in either 4D or mySQL.

4D:

A Time field, variable or expression can be in the range of 00:00:00 to 596,000:00:00

mySQL:

TIME values may range from '-838:59:59' to '838:59:59'.

Tim Penner
  • 3,551
  • 21
  • 36