Date/Time problem

[usamaalam]

Hello everybody,

Firstly, I have a datetime field in mySQL database and I want to insert system datetime whenever record is inserted into the table. If I use time() function, it returns something "1142866431". I need to save it in such a way that I can track the minutes and hour.

Secondly, I need to get all records from the same table that are past 24 hours or more.

Thanks.

[john tindell]

to get all records within the last 24 hours use something similar to this

SELECT * from table WHERE DATE_SUB(now(), interval 24 hour) >= stored_time

and to format the date stored in the table

DATE_FORMAT(stored_time,"%h:%i%p")

All the details can be found here:
http://dev.mysql.com/doc/refman/5.0/...functions.html

[usamaalam]

Thanks.

I do not want to format stored data, I just want to format it at the time of adding. Moreover, for getting records of past 24 hours, I think we also need to track date as well, it may happen that a record was saved at 10:00 PM hence, the next 24 hour will be of next day.

What do you suggest ?

[lintz]

If working with dates use John's method as it's a pain (and doesn't always return corerct results) when you save dates as 10.00pm, 09.00am etc...

[john tindell]

if you want to format it at time of adding then as long as you want the current time you can use

INSERT INTO table (date_value) VALUES (DATE_FORMAT(NOW(),"%h:%i%p"))

What do you mean by track the date? Do you want to select records from a certain time range, not sure what your looking for.

[Edit]
<reminder>Must learn to read what was posted before......</reminder>

[usamaalam]

I have a cron scheduler that runs every 3 minutes and insert some records in the database with current time. The same page then deletes the records that are past 24 hours.

Tracking the date means if record is inserted at 10:00 PM, then next 24 hours will be on the next day date, I also need to maintain this that code should successfully delete records that are 24 hours old.

Thanks a lot.