Date Questions

SimplyJoe

My dates appear in my SQL database in the following format:
1051761600
1054440000

How do I decode them in to MM DD YYYY?

Thanks

SpeedBird

date('m d Y',$timestamp);

Your database is storing Unix timestamps.

bradgrafelman

Looks like you've stored the dates as Unix timestamps. I would recommend you change these timestamps to a DATETIME format within SQL, that way they are much more manipulatable and more easily read.

You can retrieve the date in any format you wish using MySQL's FROM_UNIXTIME() function (assuming you're using MySQL, that is).

SpeedBird

To be honest, I've never had a problem using Unix timestamps in scripts provided the TZ environment variable has been set correctly. I just use date() to display a timestamp in a human-readable form.

PHP has some pretty nifty time/date functions.

bradgrafelman

SpeedBird wrote:
PHP has some pretty nifty time/date functions.

This is quite true; however, PHP isn't the one storing the dates - SQL is.

Say you wanted to SELECT all rows that have datetimes within a given range. How would you do that? You'd have to first convert the start and endpoints of the range to Unix timestamps, and then do an integer comparison. With SQL's DATETIME columns, it's a simple BETWEEN situation.

That's why I suggested going with a DATETIME column, since it would make working with the data where it is stored much easier.

SpeedBird

Oh, of course - I wasn't thinking that way 😉. I usually use strtotime() to get timestamp(s) to use in the SQL query, but FROM_UNIXTIME() would do the same job.

Richard_Clone

Ok. I wrote a whole article but I wasnt logged in so its all gone. Now the short version:

Sql stores the date in this format: year-month-day ("Y-m-d"). I presume you want to output the date as MM DD YYYY.

To do so you have to set the field in your sql DB to DATE data type. The date will be stored the way described above. To output the date the way you want it you can do the following:

$date_from_sql=$row["date"]; // is the date you retreive from the DB
$date=strtotime($date_from_sql); // strtotime converts the date in seconds.
$date_my_format=date("m d Y", $date) // you convert the seconds back to a date but to the format you like to have.

//You can do this also in one line of code:

$date=date("m d Y", strtotime($row["date"]));

I hope it helps 🙂

bradgrafelman

Or you can get rid of all of that PHP code and just incorporate it into the SQL query:

SELECT DATE_FORMAT(date_col, '%m %d %Y') AS date_col, col2, col3 FROM foo

(hence the ease of working with DATE/DATETIME columns rather than integers :p)