Converting UNIX timestamp to useable format in mySQL

wasson

Okay, so I wrote a simple blogging script that loads a UNIX timestamp into a database. The PHP script then reads the timestamp, converts it to a readable format, and displays it on a page. All that stuff has been no problem.

My problem is that I want to be able to read these entries from an archive. Like a calendar script on the side where the days that I've posted are highlighted, or at the very least have a link on the side that says "April 2005" as a link to display every post I made in April of 2005.

Of course, this is easy if I made 1 row per date variable (like month, day, year, etc.). As a UNIX timestamp, it makes my code and database table cleaner, but is it possible for a script to read that UNIX timestamp from the database and sort it as I've outlined?

Any input would be much appreciated.

rachel2004

You can convert unix timestamps to human-readable forms (e.g. April 2005) either in PHP or the db (at least in MySQL). You need to look at the date function if you wish to have PHP do it. Here is an example:

<?php
$ts = time();

// returns April
echo date('F', $ts)."<br />";

// returns 2005
echo date('Y', $ts)."<br />";
?>

It's better to do these things in the database. If you are using MySQL, check the MySQL manual for the same function name: "date". It will have all of the options you need for formatting.

Drakla

Adding to the above, if you do want to do it as part of the select use FROM_UNIXTIME(timefield, formatstring)

The format string differs from the php version, but do a search and you'll find a list of all the available replacement letters.

wasson

I'm really not bright at this stuff just yet. Here's the code I'm trying to use:

$result = mysql_db_query($database, "SELECT * FROM_UNIXTIME(date)") or die (mysql_error());
if (mysql_num_rows($result)) {
while ($qry = mysql_fetch_array($result)) {
echo "$qry";

I've tried all sorts of combinations and looked up all sorts of samples, and I'm just not figuring it out.

Just to clarify, the date function is being inserted and read by PHP just fine, but I'd like to write a mySQL query string to grab every month that I've made post, so that it can output a list like this:

March 2005
April 2005
June 2005
etc.

The text would link to a page that displays every post made in that month. I'm sure this has something to do with the aforementioned FROM_UNIXTIME, but I'm just not getting it. Any help is greatly appreciated.

rachel2004

$sql = "SELECT date_format(dateColumn, '%M %Y') AS MyDates FROM TableName";