Page 1 of 2
date() timestamp issues
Posted: Tue Jun 27, 2006 4:50 pm
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?
Posted: Tue Jun 27, 2006 4:55 pm
by feyd
strtotime() may be of interest, but it can interpret the strings wrong at times, so I often go with an clever
explode()
Posted: Tue Jun 27, 2006 4:57 pm
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:
Posted: Tue Jun 27, 2006 5:16 pm
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.
Posted: Tue Jun 27, 2006 5:24 pm
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?
Posted: Tue Jun 27, 2006 5:26 pm
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?
Posted: Tue Jun 27, 2006 5:28 pm
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().
Posted: Tue Jun 27, 2006 5:30 pm
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).
Posted: Tue Jun 27, 2006 7:38 pm
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
Posted: Tue Jun 27, 2006 7:40 pm
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.
Posted: Tue Jun 27, 2006 7:48 pm
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
Posted: Wed Jun 28, 2006 6:30 am
by Jenk
You could also make use of
sscanf() if you don't want to depend on your RDBMS.
Posted: Thu Jun 29, 2006 1:26 pm
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.
Posted: Thu Jun 29, 2006 3:23 pm
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.
Posted: Thu Jun 29, 2006 9:26 pm
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.