[Resolved] formatting an historical date

tomalatski

Hi there,

I am using PHP and MySql and have retrieved a date from the database which is in the format YYYY-MM-DD and I want to format it to something more "friendly".

Can anyone help please - I have looked in all the Date and getdate functions but none seem to assist.

Rgds,
Tomalatski

laserlight

What is more "friendly"?

You could use strtotime() followed by date()

ristuart

To format a date nicely, as you probably know, you can use the date() function. This can take two arguments, the first is a format string to determine the format of the output, and the second, optional, argument is a timestamp. Without the second argument it'll give you the current date nicely formatted, but you can format any timestamp as well.

This means you'd need first to convert your extracted date string into a timestamp, and then convert that to something nice with date(). You could try using strtotime() to do this, or maybe just parsing the day, month and year out yourself and using mktime() to get the timestamp.

However... what does that 'historical date' in your subject title mean? In case you are dealing with actual historical dates bear this in mind...

PHP native date functions use integer timestamps for computations. Because of this, dates are restricted to the years 1901-2038 on Unix and 1970-2038 on Windows due to integer overflow for dates beyond those years.

But I guess you might just mean dates were stored in the database in this unfriendly format some time ago?

Anyway, hope this helps!

ristuart

tomalatski

Let me explain:
What I am trying to do is to create a 24 hour log where people type in incidents of note for managers to read on a daily basis.

The date is created automatically from the system/server date and formatted into MySql is YYYY-MM-DD

When I want to retrieve the Log for Yesterday I want it to read something like "Wednesday 11th June 2003" which will be the title of the report.

Should I have formatted the date differently when inserting into MySql?

Tomalatski

pyro

Why not just format it how you want it when inserting it?

$date = date ("l jS F Y");

--EDIT--

Just though of something else... Try this:

<?PHP

$date = date ("Y-m-d");
list ($year, $month, $day) = split ("-", $date);
echo date ("l jS F Y", mktime (0,0,0,$month,$day,$year));

?>

ristuart

Hmm, if when you store it in the database you stored the date as a timestamp, then whenever you read it out you can format the date how you like. This seems to me the best way to store dates in the database.

However, it can be done using the format you've stored the dates in at the moment. Rather than create a code example from scratch, I'll use and slightly modify some code I saw here earlier.

//Assumes you've read the date from the database into $date_input

//allows you to change database stored format easily later on
$format = "yyyy-mm-dd";
$pos = array(); 
$pos['year'] = strpos($format, "yyyy"); 
$pos['month'] = strpos($format, "mm"); 
$pos['day'] = strpos($format, "dd"); 

//Extract the year/month/day from $date_input
$date_year = substr($date_input, $pos['year'], $pos['year'] + 3); 
$date_month = substr($date_input, $pos['month'], $pos['month'] + 1); 
$date_day = substr($date_input, $pos['day'], $pos['day'] + 1); 

//Get the timestamp for that date
$date_timestamp = mktime( 0, 0, 0, $date_month, $date_day, $date_year);

//Finally, use the timestamp to generate a date() formatted date
$date_friendly = date("l jS F, Y", $date_timestamp);

/* Adapted from  matt_4013's example at...
http://www.phpbuilder.com/board/showthread.php?threadid=10243071 */

So, if the $date_input read from the database was "2003-06-12" then $date_friendly would come out with that date format string as 'Thursday 12th June, 2003". To change that format, look up the date function in the PHP manual - I gave the link in my first post above.

You might be able to use strtotime() as mentioned above rather than extracting the year/month/day from the database date, but I've never used that myself.

Hope that helps,
ristuart

Edit: But the code given above while I was posting looks much better! I'd go for that, or for storing the date as a timestamp to start with and then using date() to format it however you like when you've read it out. Probably more future proof that way, in case you need to use it in a different format at all.