Datetime
Moderator: General Moderators
Datetime
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?
current time is less than the `start` + `length` *86400
I think that will do it.
Code: Select all
SELECT * FROM sometable WHERE UNIX_TIMESTAMP() < `start` + `length` *86400;I think that will do it.
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).
http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html
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);