trouble understanding dates

emrys404

I am creating a site that relies heavily upon dates. In my database i have 3 date fields. I need to run these dates with date() and strtotime() functions mainly. I seem to have problems running these functions on the DATETIME and TIMESTAMP formats that come out of the mysql database. Do these functions require a unix time format?
Let me clarify my use for strtotime():

date("F j, Y", strtotime("Thursday", $blar));

where $blar is a value of either DATETIME OR TIMESTAMP formatting and not a current time.
Should i convert $blar to unix time as i draw it out of the database, or can i run some other functions on it? Which type is better to have my date fields in mysql?

Arrgh, i'm just confusing myself.
THanks for any and all help.

ScrewLoose

You are correct in thinking that PHP's date functions require the time to be in the unix time format. To retrieve a date from mysql and have it formatted as a unix time your query should be formatted similar to the following:

select UNIX_TIMESTAMP(date) from mytable

yahelb

you should decide what should be the field in the db,
datatime or unix timestemp.

as far as i undestand datetime is less effisient but more readable.
i choosed datetime cause it is really comfortable for see and debug.

now lets this you get date (datetime) from mysql:


$sql = "SELECT date_field FROM table";
$result = mysql_query($sql) or die(mysql_error());
if( $row = mysql_fetch_array($result) ) {
    do {
          // convert the date to unix timestemp
          $u_date = strtotime($row["date_field"]);

      // format it (offcourse there is no need to reformat...
      $my_date = date("d/m/y G:i:s", $u_date);
} while(row = mysql_fetch_array($result));
}

hope this is help.

colbec

Does strtotime("Thursday"... make sense? Which Thursday? Next Thursay, last Thursday, the second Thursday of this week next year at least tie it down a bit more...

Weedpacket

Originally posted by colbec
Does strtotime("Thursday"... make sense? Which Thursday? Next Thursay, last Thursday, the second Thursday of this week next year at least tie it down a bit more...

Without qualifiers, strtotime takes it to mean "the first Thursday on or after the date in question". At time of writing, it's Thursday today, and so strtotime('Thursday') is today, strtotime('Friday') is tomorrow, and strtotime('Wednesday') is next week - same Bat-time, same Bat-channel.

And yes, the timestamp format strtotime() and PHP's other date/time functions uses is the conventional Unix form (seconds since the start of 1970), rather than MySQL's own format.