Page 1 of 1

Datetime

Posted: Sun Dec 04, 2005 10:10 pm
by Todd_Z
if i have a table with fields such as `Start` [datetime] and `Length` [int] and I want to select all rows where the current time is less than the `start` + `length` *86400 [secs/day], how do i do this?

Posted: Sun Dec 04, 2005 10:20 pm
by neophyte
current time is less than the `start` + `length` *86400

Code: Select all

SELECT * FROM sometable WHERE UNIX_TIMESTAMP() <  `start` + `length` *86400;

I think that will do it.

Posted: Mon Dec 05, 2005 3:36 am
by onion2k
You can use timestamps, but you might encounter two possible problems:

1. British Summer Time .. if you live somewhere where the time changes by an hour a couple of times a year then you'll get small errors where you try to add days across the boundary when the time changes.

2. If you need to use dates before 1970 or after 2038 then timestamps aren't sufficient.

Fortunately MySQL has a bucketload of handy date manipulation functions built in .. including the DATE_ADD function (or ADDDATE .. they're aliases of each other).

Code: Select all

SELECT * FROM sometable WHERE NOW() <  DATE_ADD(`start` INTEVAL `length` DAY);
http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html