In standard SQL, TIME is limited to 23 hours, 61.999... seconds.
If you want to record a duration, there is an INTERVAL data type, or
actally there are two: DAY-TIME intervals and YEAR-MONTH intervals. You
can specify which components to include, the maximum range (in number of
years or days or whatever) and the precision (down to at least microsecond).
Thanks,
Roy
Tim Soderstrom wrote:
On Dec 29, 2008, at 10:19 AM, Jay Pipes wrote:
Tim Soderstrom wrote:
On Dec 29, 2008, at 9:11 AM, Jay Pipes wrote:
From the MySQL Manual:
http://dev.mysql.com/doc/refman/5.0/en/time.html
"MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or
'HHH:MM:SS' format for large hours values). TIME values may range
from '-838:59:59' to '838:59:59'. The hours part may be so large
because the TIME type can be used not only to represent a time of
day (which must be less than 24 hours), but also elapsed time or a
time interval between two events (which may be much greater than 24
hours, or even negative)."
Question:
Does anyone use the TIME column type?
Yes. I use it to store song lengths. I assume the same holds true for
people wanting to store videos, etc.
OK. So, basically, you are just using TIME as a presentation thing,
right? TIME outputs automatically as HH:MM:SS, and that is good for
your application?
Well, yes, I suppose I am. But in this case, I think it's warranted
because I'm only handling time (duration specifically). So, it is
presentation, but it's also a bit of consistency. I want to say "this
field only stores times. That's it. Not numbers. Times". To me,
consistency is part of what a modern SQL-based DB is for. This is as
convenient as having an IPv4 data-type. If you know it's always going to
be an IP, why not have the DB give it to you as an IP and throw an error
when it isn't? Same thing for the time type (or, again, more
specifically for me, duration). Yes, you can store it as a raw integer,
but you can do that with IPs now and look how well that works :)
If we remove TIME, the people that now use it, would probably just store
it as a string (much like they do for IPs). Should Drizzle cater to the
lazy and uneducated? Perhaps not, but I lost count of the times I've had
to discuss INET_ATON and NTOA with a customer. I can't imagine TIME_STON
and TIME_NTOS :)
Now, to draw the line, I'd like to have a TIME type, but I don't really
think it would be the place of the DB to provide various formats to
display the time. Only one - namely HH:SS. If I want to display that as
"17 hours, 9 minutes" that is something that application should do. Or
at least something Drizzle shouldn't provide as a core offering.
To be fair, if the TIME type can be made into a plugin I'd be happy
pulling it out of the core of Drizzle.
If so, do you use the "large hours" ability to store hours > 23:59:59?
I haven't, but this came up once. Someone wanted to track the total
length of time someone was logged in. And, in fact, it was thought
that he might need MORE than 839 hours :) I wouldn't really say
there's a huge need for this, however. Though, I would like to keep
the TIME field. For storing reasonable lengths (again songs), it's
wildly convenient.
What about storing the elapsed time in seconds in an integer column?
For that application, that's what he ended up doing. And that works -
the formatting is done in the application layer - presentation, in other
words. But, in an ideal world, it would have been nice to store
YY:DD:HH:SS and, if MySQL had it, I would have suggested he use it.
Again, it goes back to consistency. An integer is just an integer. It
doesn't mean anything, or rather it could mean anything. TIME/DURATION -
that at least describes what I'm storing and puts constraints around it.
I'm looking to simplify the temporal column types and this is one
type I believe may possibly be up for the chopping block, but
certainly want to get people's opinion on it.
Well, since Drizzle is going to be pluggable, is this something that
can be a plugin and simply be built from native storage types? Like
human-readable IPv4 addresses, the underlying storage could just be
an integer. When you store or call it, you could just automatically
run a function to present it in a friendly way? That may be already
what you are doing with the TIME field and I can't claim to be an
expert but thought I'd throw that out there :)
Possibly. Right now, I'm just trying to simplify both the internal
implementation of TIME and the external "weirdness" of the 838 hours
thing...
Well, I also noticed it consumes 3 bytes ala MEDIUMINT, which I know was
axed. Perhaps you can, instead of using a TIME type, use DURATION and
store only positive numbers, even in a smaller range. *shrug* I've only
used TIME for duration, so I can't really speak to its other uses.
Tim S.
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
