Date Manipulation

[Anon]

OK, stupid question, but I can't find the answer.

I'm pulling some dates out of MySQL that are in DATE format: YYYY-MM-DD. I want to give them in human friendly January 10, 2001 format WITHOUT having to do a complex explode and replace operation on every single date (when I might have up to 350 or 400 on a single page).

Is there a real easy and low-overhead way of doing this?
-john

[Anon]

There is a function called strtotime() which parses English textual datetime strings into a UNIX timestamp. I can't remember whether that works with mySql DATE format - I think you might have to change it with something like this (taken from the PHP manual page):

ereg_replace('([0-9]).([0-9]).([0-9]*)','\2/\1/\3', $s)

to turn it into American date format. Even so, that might be a bit more efficient than lots of string explodes, etc.

You can then use date() to turn that timestamp into a human readable string in any format you want.

Kevin.

John Koetsier wrote:

OK, stupid question, but I can't find the answer.

I'm pulling some dates out of MySQL that are in DATE format: YYYY-MM-DD. I want to give them in human friendly January 10, 2001 format WITHOUT having to do a complex explode and replace operation on every single date (when I might have up to 350 or 400 on a single page).

Is there a real easy and low-overhead way of doing this?
-john

[Anon]

Just a quick follow up:

Another look at the user comments on the PHP manual page for strtotime() reveals another approach. You can get mySql to select a Unix timestamp version of the DATE field:

"SELECT ..., UNIX_TIMESTAMP(my_datetime_field) FROM ..."

So you can just pass this value into the date() function.

Kevin

[Anon]

Freaking awesome!
:-)

Thanks, Kevin!
-john

[Anon]

How about this....MySQL has a date_format() function where you can format your date columns anyway you want.

For a result like this: January 1, 2000

Then use this as your select statement:

SELECT DATE_FORMAT(your_date_column,'%M %e,%Y') AS formatted_date FROM your_table

Then, once you fetch the row, just print out $row[formatted_date], it'll come out exactly like above. No extra stuff to do.

There are tons of date and time and everything functions to use in MySQL. It's a whole lot more than SELECT * FROM...

Read the manual: www.mysql.com/documentation/mysql/bychapter/

For date and time functions: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Date_and_time_functions

---John Holmes...

[Anon]

True, but sometimes you can't choose in what form you get your data.

Or, it's just too troublesome to get people to give you data the exact way you want it!

[Anon]

You said you were pulling data out of a DATE column in a MySQL database, right? Well, the easiest way to format a DATE is to use DATE_FORMAT(). Do your formatting in MySQL instead of converting it to unix, and then doing PHP commands. You're creating extra work for yourself. DATE_FORMAT() is the MySQL equivilent of date() in PHP, you just cut out the conversion to unix_timestamp, so it makes your script faster.

---John Holmes...

[Anon]

Sheesh, there's more to MySQL than insert, select, and delete?

Now I have to re-evaluate my whole site!
<grin>

Seriously, that's obviously (now) the best way to go. Thanks!

-john

[Anon]

Oh man..don't get me started. Do you know how many people I see on all these forums who don't know you can do other things besides "SELECT * FROM table"??? They think that's all there is.

Happy programming...

---John Holmes...