date time question

[coderwasp]

I would like to insert a mysql db date / time stamp so that I can return it later in the format:

March 20, 2007, 12:56 pm

my question is should I have the db field as datetime or timestamp and what is the function that generates the timestamp?

also I need this to be sortable by date.

 

[BiJae]

I found this script a few years back and it's proved invaluable:

function mysql_date_to_human($dt){
      //intercept mysql's default ZERO date value.
	  if ($dt=="0000-00-00") return "";

                  $yr=strval(substr($dt,0,4));
                  $mo=strval(substr($dt,5,2));
                  $da=strval(substr($dt,8,2));

                  return date("F j\, Y", mktime (0,0,0,$mo,$da,$yr));
          }

All you need to do to get the function to work for a timestamp field is add variables for the substr in the timestamp field. Next replace the 0's in the mktime() function with the corresponding variables. Then use your parameters in the date() function to format the output you would like to display

http://us3.php.net/manual/en/function.date.php

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[vacunita]

PHP has a handy dandy Date() function that takes a unix timestamp.

Mysql also has a Date_Format you can use.

The online manuals for either one are you friends for additional info on each.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

your question was

should I have the db field as datetime or timestamp and what is the function that generates the timestamp?

I would recommend creating your db field as an integer and storing the date time as a unix epoch value. this is sortable and manipulable using the php date() function as Vacunita says.