php/mysql time questino

[ringo747]

Hi, I posted a while back about inserting the current time into a mysql db and was told to do so using the now() function.

When I recall this value it looks something like:
2007-02-24 14:59:42

I would like to format this as

Monday 24th Feb 2007, 14:59 (drop the tenth of seconds)

Anyone know how I can do this? I really do not have a clue. Also I want to be able to format the date to say 'today' or 'yesterday' if appropriate otherwise display like the format i mentioned.

Would appreciate any help on this.

 

[matja]

mysql> select date_format('2007-02-24 14:59:42', '%W %D %M %Y, %H:%i');
+----------------------------------------------------------+
| date_format('2007-02-24 14:59:42', '%W %D %M %Y, %H:%i') |
+----------------------------------------------------------+
| Saturday 24th February 2007, 14:59                       |
+----------------------------------------------------------+
1 row in set (0.06 sec)

edit: Oh, you wanted the short month... %b instead of %M, then.

 

[ringo747]

cheers for the help - it makes sense ok but i'm just not sure how I will use it to format my output.

Basically I am echoing stuff out of a database so my line of code for the time is like this:

echo $row['time_added'];

I have one query that pulls everything out of the database and echoes it all out at once. Do I need a separate query to select which format the time is pulled out in?

Cheers

 

[ringo747]

anyone?

 

[Subliminal Aura]

Best read the php manual mate :

Heres something from a quick google :

http://www.ucl.ac.uk/is/mysql/php/

I draw your attention to this loop :

# Fetch table rows, one by one, and display as HTML table rows
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>", $row['Name'], "</td><td>", $row['Age'],
"</td></tr>\n";
}

 

[matja]

Yes just use date_format() function on your column name, expression, whatever.

select date_format(my_table.my_column, '%W %D %M %Y, %H:%i') as my_date;

Then use a mysql_fetch_row()/assoc()

 

[Fourstar]

You can also do the date processing in PHP. I've just started with PHP so I did this for a little practice. It might not be the best way.... but it does work!

The important functions are:
mktime() - Get Unix timestamp for a date
strtotime() - Parse about any English textual datetime description into a Unix timestamp
date() - Format a local time/date
Follow the links for more detailed information.

To hold the dates I created the following table:

CREATE TABLE `DateTest` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`)
);

<?php

	@mysql_connect("localhost","username","password")
		or die ("could not connect to DB");

	mysql_select_db("test")
		or die("unable to connection to database");

	/*
		These two variables are setup ready for working out if a given
		date is today or yesterday.

		mktime produces a timestamp in seconds between the Unix epoch
		(midnight, Jan 1st 1970) and a given date and time.

		With the dates converted to numbers it is easy to add and subtract them

		$todaystart is set to midnight of todays date
		$yesterdaystart is exactly 1 day earlier
	*/

	$todaystart = mktime(0,0,0,date("n"),date("d"),date("Y"));
	$yesterdaystart = $todaystart - 86400; // seconds in a day

	$query = "SELECT * FROM DateTest ORDER BY id";
	$result = mysql_query($query);

	//loop through the results
	for ($count=0; $count < mysql_numrows($result); $count++)
	{
		$id   = mysql_result($result, $count, "id"); //int
		$date = mysql_result($result, $count, "date"); //datetime
		
		//convert our date to a numeric timestamp
		$dateasnumber = strtotime($date);
		
		print("id: ".$id.", date: ".$date." => ");

		if ($dateasnumber >= $todaystart)
		{
			print("Today");
		}
		elseif ($dateasnumber >= $yesterdaystart)
		{
			print("Yesterday");
		}
		else
		{
			//format date nicely eg "Monday 24th Feb 2007, 14:59"
			print(date("l dS M Y, H:s",$dateasnumber));
		}

		print("<br>");
	}

	mysql_close();

?>

The script outputs:

id: 1, date: 2007-03-06 11:32:27 => Today
id: 2, date: 2007-03-06 00:00:01 => Today
id: 3, date: 2007-03-06 00:00:00 => Today
id: 4, date: 2007-03-05 23:59:59 => Yesterday
id: 5, date: 2007-03-05 15:40:21 => Yesterday
id: 6, date: 2007-03-04 04:40:22 => Sunday 04th Mar 2007, 04:22
id: 7, date: 2007-03-02 11:50:06 => Friday 02nd Mar 2007, 11:06
id: 8, date: 2006-06-09 11:51:15 => Friday 09th Jun 2006, 11:15

Let me know if you have questions and I'll try my best to help

 

[Sic]

Yes just use date_format() function on your column name, expression, whatever.

select date_format(my_table.my_column, '%W %D %M %Y, %H:%i') as my_date;

Then use a mysql_fetch_row()/assoc()

what he says. MySQL has far better support for date in general, and as you're going through your db for data to display, you might as well bring it back formatted how you want, if you can

 

[Fourstar]

I've updated my script with the advice above.

Can anyone say if it is a good way of solving the OP's question?

<?php
	/*
		Create numeric timestamps to use when working out if a date is
		today or yesterday.

		$todaystart is set to midnight of todays date
		$yesterdaystart is exactly 1 day earlier
	*/

	$todaystart = mktime(0,0,0,date("n"),date("d"),date("Y"));
	$yesterdaystart = $todaystart - 86400; // seconds in a day

	$query = "SELECT id, date_format(date, '%W %D %M %Y, %H:%i') as date FROM DateTest ORDER BY id";
	$result = mysql_query($query);
	
	//loop through the results
	while($row = mysql_fetch_assoc($result))
	{
		$id = $row['id'];
		$date = $row['date'];

		//convert our date string to a numeric timestamp
		$datetimestamp = strtotime($date);

		echo "Row id: $id, Date: $date, Formatted: ";

		if ($datetimestamp >= $todaystart)
			echo "Today";
		elseif ($datetimestamp >= $yesterdaystart)
			echo "Yesterday";
		else
			echo $date;

		echo "<br>";
	}

	mysql_close();

?>

 

[ringo747]

thanks for all the help on this guys. Got everything sorted out.