Jay,
this looks good to me! The standard opens for a split-second precision
as well, but if noone can come up with a good use case, it's probably OK
to just forget about that.
Too bad we do not have 17-bit integers, by the way :)
Thanks,
Roy
Jay Pipes wrote:
OK, this actually gets back to my original (probably poorly worded)
question:
QUESTION: Is there a need for the current MySQL TIME behaviour of
allowing hours up to 838?
If we could make the TIME column type support seconds in the range of a
single day, life would be much easier.
So...
Can we do this?
The result would be the following:
TIME would still be stored in 3 bytes.
TIME would store seconds in the range 00:00:00 (midnight) to 23:59:59.
For those currently using the TIME column type to store *elapsed time*:
* If the range of elapsed time needing storage is > 00:00:00 - 23:59:59
(86400 seconds), then you would use an INTEGER type to store seconds.
* If the elapsed seconds will always be within the above range, continue
to use TIME.
Incoming formats of TIME would be limited to:
HH:MM:SS
H:MM:SS
MM:SS
M:SS
SS
S
And H would be enforced in the range 0-23, M and S would be enforced in
the range 0-59.
Note to Roy:
We won't need to store time in the range up to 61.999 seconds because we
do not support timezones.
So, all, please let me know what you think of the above proposal. It
would simplify the way we handle TIME columns internally and, I believe,
make the TIME column type simpler and more straight-forward to
understand for users.
Cheers,
Jay
Roy Lyseng wrote:
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
_______________________________________________
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
