This project is archived and is in readonly mode.

#5788 ✓invalid

mysql time

Reported by jerome187 | October 11th, 2010 @ 06:40 PM

setting something in migration as .time and the resulting table contains date when its supposed to be just time.

this in migration

t.time :time, :default => "00:00:00"

this is what schema ends up being after running the migration

t.time "time", :default => '2000-01-01 00:00:00'

Comments and changes to this ticket

  • Ryan Bigg

    Ryan Bigg October 11th, 2010 @ 11:33 PM

    • State changed from “new” to “incomplete”
    • Importance changed from “” to “Low”

    Please submit a failing test case as a patch as per the guide so that this issue can be resolved.

  • jerome187

    jerome187 October 12th, 2010 @ 05:12 AM

    • Tag set to activerecord

    no problem

    how is this test?

  • Aditya Sanghi

    Aditya Sanghi October 12th, 2010 @ 08:52 AM

    • State changed from “incomplete” to “invalid”

    Rails maps SQL types to Ruby classes, SQL time gets mapped to Ruby's Time class. Time class in ruby has a date component. Rails defaults this to 2000-01-01. If you need to work on the time component, you need to regard bonus_time as an object of Ruby's Time class. If you need to read the Time component of bonus_time, you should refer to the Time class' documentation and send required methods.

    To get "00:00:00", you would do t.bonus_time.strftime("%H:%M:%S") or t.bonus_time.to_s(:time) to get "00:00".

  • jerome187

    jerome187 October 12th, 2010 @ 03:31 PM

    right my bad for using strings for comparison. well ok take a look at this test, if you try to save a time with more than 24 hours, no joy.

  • Aditya Sanghi

    Aditya Sanghi October 12th, 2010 @ 09:41 PM

    Not sure what the use case here is, but 25:00:00 does not parse as a time, it will throw argument out of range.
    If you want to store the semantic "duration", you would be better off with another field not a time sql type.

  • jerome187

    jerome187 October 12th, 2010 @ 09:58 PM

    quote "

    A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but permits assignment of values to TIME columns using either strings or numbers. "

    I want to use it for markers in video content. Having a limit of 24hrs when -838 to 838 hrs is available in mysql (not sure of other databases) seems, uh, lame, for lack of better word.

    I found it easy to write the test, I could take a crack at implementation as well.

  • Aditya Sanghi

    Aditya Sanghi October 12th, 2010 @ 10:56 PM

    Awesome, please do send a patch.

    However, I do maintain, if you want to use video markers, you would be better off storing seconds from start in an integer field.

  • jerome187

    jerome187 October 12th, 2010 @ 11:13 PM

    We will want milliseconds or at least tenths of a second too now that I think about it. There does not seem to be support for milliseconds in any of the mysql datatypes. Probably better off storing as a floating point disguised as string, or HH:MM:SS:hundredths of a sec

    I guess means I will not implement it, at least for the time being, as I don't think we will use it.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>