date() timestamp issues

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

date() timestamp issues

Post by mjmacarty »

I am trying to pull a date out a MySQL database with the following format: yyyy-mm-dd, a non-timestamp date. I want to pull things out like the day of the week and other date type niceties. Here is some code, but I really probably just need a function name to work with:

Code: Select all

for ($y=1; $y<=$count; $y++){
	  $row =mysql_fetch_array($result);
	  $start = date('l F jS',$row['startdate']);
	  $num = $row['eventnum'];
	  $name = $row['name'];
	  $buyin = $row['buyin'];
	  $time = $row['starttime'];
	
echo	"<tr>
		<td>$start</td>
		<td>$num</td>
		<td>$name</td>
		<td>$time</td>
		<td>$buyin</td>		
		</tr>";	
	}
So this shows up as Wednesday December 31st I am guessing 12/31/69?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

strtotime() may be of interest, but it can interpret the strings wrong at times, so I often go with an clever explode()
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post by Robert Plank »

A non-timestamp date so you mean the date field is of DATETIME? Or are you saying the YYYY-MM-DD is being stored as a field of characters?

Show us your query. Either you will have to do a "UNIX_TIMESTAMP(startdate) AS startdate" in the query, or format it as a string right in mySQL using DATE_FORMAT().

When all else fails:

Code: Select all

echo $row['startdate'];
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

Post by mjmacarty »

I wanted to handle it back in the query, but my hosting company is running MySQL 4.0.x. Time for a new hosting company? It is a DATETIME data type though.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What exactly is the issue? MySQL has some pretty nifty date and time functions to handle dates. What is currently in the database and what do you want it to look like?
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

Post by mjmacarty »

Cool. strtotime() did the trick. The content is not dynamic so maybe I won't get any errors? I don't think I am clever enough to make explode work. So you explode() and then mktime() something like that?
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post by Robert Plank »

mjmacarty wrote:I wanted to handle it back in the query, but my hosting company is running MySQL 4.0.x. Time for a new hosting company? It is a DATETIME data type though.
If it is DATETIME then definitely use mysql's DATE_FORMAT() function (look it up)... it will be faster than strtotime().
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Here is the MySQL manual page on date and time functions...

If you can handle the formats DB side it will take load of the HTTP server (making the page output faster).
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

Post by mjmacarty »

I think the biggest issue is the hosting company I am using is running the las t version of MySQL before all the coolest functionality was added. Hence I have to find innovative ways to write extra code.

Most everything good in MySQL comes after version 4.0
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

Post by mjmacarty »

Well thanks to everyone for their input. I have this working. Perhaps not the most elegant solution, but sometimes life just isn't elegant.
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post by Robert Plank »

mjmacarty wrote:Most everything good in MySQL comes after version 4.0
DATE_FORMAT() has been around since at least version 3.23 man
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

You could also make use of sscanf() if you don't want to depend on your RDBMS.
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

Post by mjmacarty »

DATE_FORMAT() has been around since at least version 3.23 man
My bad. Well, anyway more than one way to skin a cat.
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post by Robert Plank »

More than one way but strtotime() is the slowest and sloppiest... either use:

* DATE_FORMAT to have mysql format the string for you or:
* UNIX_TIMESTAMP to output the date as a unix timestamp and then hand it over to date() in PHP

Taking something that's already in a numeric date format, outputting it as a string then walking through the string to get it back to the data type it was originally is just silly.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Not mention that the DB server can handle quite a bit more than straight select queries in a fast way. Takes a little load of the PHP engine.
Post Reply