entering date/time best method?

[agentsmith]

I have an application that will have the user capture the creation date and an "expiration" date.

I want to show these records in a table -- sorted by this expiration date.

I have created the table and already have the records fill each row by jobID, however it would make more sense to have the records be viewed by this "expiration date"

Finally, I would also like to keep track of time spans within records. For instance. Being able to calculate (by number of days...and if needed hours) the amount of time that passed from the time the record was inputted into the system, until the time it was considered "completed" or "fulfilled" -- it doesnt matter if this date was prior to or after the "expiration" date.

Let's say the record was created on June 1, 2003, it has an expiration date of July 3, 2003, and the current date is June 19, 2003.

I would like to show that the record is currently 18 days old, or if "completed", that the record took 18 days to be "completed".

My questions are :

1. What is the best way to collect this data (month, day, year) ?

2. What datatype9s) should I be using? Should it be done from the mySQL side or the PHP side?

Is there a tutorial that covers this type of need specifically? Thanks.

I'm sure that there are a couple of solid ways to do this.

[lanjoky]

I would do it on the mySQL side making your expiration filed and start field of type timestamp or date ... when you enter it into the data base you can use variations of the php now() or mktime() funtion to enter in the dates you want ... then when pulling your records you can "Select (whatever) FROM TableName ORDER BY time_field desc" to pull the records out for displaying...

here are a few links for you:

1. http://us2.php.net/manual/en/function.date.php ( manual reference for the date() function may want to use it instead of now())

2. http://us2.php.net/manual/en/ref.mysql.php (manual referance for mysql functions that may help you)

3. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DATETIME (MySQL referance page for the date and timestamp field types)

Hope this helps

GL

[ucbones]

agentsmith

As a personal preference, I like to store my dates as UNIX tstamps (i.e. the time in seconds since 1970), as these are very easy to manipulate into whatever format you like, and very easy to generate (just use mktime(), no arguments required!).

Just by £0.02...

ucbones

[cgraz]

Originally Posted by lanjoky
you can use variations of the php now() or mktime() funtion to enter in the dates you want

Actually, NOW() is a MySQL function, not PHP. Not to be picky, but I thought it would be best to clarify. Anyone coming across this post might try something like

<? echo now(); ?>

and not understand why it doesn't work. Knowing that it's a MySQL function might save a few people some headaches.

Cgraz

[lanjoky]

RT! thanks for the clarification 🙂 ... I kinda knew that but didnt ... I use it all the time properly ... just got confuzed I guess 🙂 ... thanks