Datetime

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Datetime

Post 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?
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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
Post Reply