What's the largest possible timestamp value before it rolls over?
I'm trying to figure out what's the best way to do the following. I
have a table that has these three fields in it:
priority int(3)
end_time timestamp(14)
start_time timestamp(14)
NOTE: only start_time gets inserted as Now() (or NULL so it
automatically gets set) end_time is a variable that gets set depending
on what the user picks for time period.
And I'd like to select and order like:
select * from table order by priority, end_time, start_time;
Now, some rows will have a valid start, and end times. Others will
have only a valid start time, and an undefined end time, or infinite if
you will. However, if I don't fill in a value for that field, MySQL
dutifully puts in 00000000000000 for me, and when I perform the above
query, I get something like this:
+----------+----------------+----------------+
| priority | end_time | start_time |
+----------+----------------+----------------+
| 0 | 00000000000000 | 20010329023143 |
| 0 | 20010330231405 | 20010329021805 |
| 0 | 20010331012712 | 20010330162312 |
| 10 | 20010422051135 | 20010419161205 |
| 10 | 20010422051135 | 20010419162335 |
| 50 | 20010330131222 | 20010329015222 |
| 50 | 20010330131703 | 20010329015703 |
| 100 | 20010331192630 | 20010329014630 |
| 100 | 20010401211100 | 20010330023100 |
+----------+----------------+----------------+
However, I want that first row, to end up last within that priority
level (so drop it to line 3). The only thing I can think of is to put
in some insanely large value for that timestamp, so that the select
clause works.
So, I need to figure out what's the largest possible amount of time
I can put in, and make it like a 'dead end' value. Anything that has
that value is considered infinite. I suppose I can put in
20380101000000...but is that the ceiling of MySQL?
Unless there's another way?
--
W |
| I haven't lost my mind; it's backed up on tape somewhere.
|____________________________________________________________________
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Ashley M. Kirchner <mailto:[EMAIL PROTECTED]> . 303.442.6410 x130
SysAdmin / Websmith . 800.441.3873 x130
Photo Craft Laboratories, Inc. . eFax 248.671.0909
http://www.pcraft.com . 3550 Arapahoe Ave #6
.................. . . . . Boulder, CO 80303, USA
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
