RE: largest timestamp value

2001-04-24 Thread Batista, Facundo 

Did you try to put (-1) in it?

Bye!


Facundo Batista
Gestión de Red
[EMAIL PROTECTED]
(5411) 4819-4358





#---Mensaje original-
#--De: Ashley M. Kirchner [mailto:[EMAIL PROTECTED]]
#--Enviado el: Viernes 20 de Abril de 2001 8:15 PM
#--Para: MySQL Mailing List
#--Asunto: largest timestamp value
#--
#--
#--
#--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 00 for me, and when I perform the above
#--query, I get something like this:
#--
#--+--+++
#--| priority | end_time   | start_time |
#--+--+++
#--|0 | 00 | 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
#--2038010100...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
#--

largest timestamp value

2001-04-20 Thread Ashley M. Kirchner 


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 00 for me, and when I perform the above
query, I get something like this:

+--+++
| priority | end_time   | start_time |
+--+++
|0 | 00 | 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
2038010100...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