Inserting PHP Dates Into MySQL

[Joseph Witchard]

If you format how today's date is supposed to look in PHP, and then insert it into MySQL (into either a date or datetime field, because I don't know the difference), will it stay formatted correctly when you take it out of the database?

[skywalker2208]

date is just the date. For exampe YYYY-MM-DD

datetime is the date and time so YYYY-MM-DD 00:00:00

You can insert your php date as long as it is in the format above depending on which one you choose.

[NogDog]

You can control the format when it is retrieved from the DB via MySQL's DATE_FORMAT() (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format) function.

[Joseph Witchard]

You can control the format when it is retrieved from the DB via MySQL's DATE_FORMAT() (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format) function.

Would that just go in the SQL query you construct with PHP?

[NogDog]

Yes, e.g.:

$sql = "SELECT `title`, DATE_FORMAT(`date_submitted`), '%Y/%m/%d') AS `date` FROM `some_table`";

[Joseph Witchard]

What if your query was getting a lot out, like:

post_id
author_id
posted_by
post_title
date (obviously)
post_body

How would it work when you pulled that much out?

[ariell]

Not any different. And, by the way, six columns is all but much.

[NogDog]

I just noticed I had a typo in my last response. There's an extra ")". It should be:

$sql = "SELECT `title`, DATE_FORMAT(`date_submitted`, '%Y/%m/%d') AS `date` FROM `some_table`";

[NogDog]

What if your query was getting a lot out, like:

post_id
author_id
posted_by
post_title
date (obviously)
post_body

How would it work when you pulled that much out?
If it really bothers you to take a minute to type out the column names, you could always do:

SELECT *, DATE_FORMAT(`date`, '%Y/%m/%d') AS `date_ymd` . . .

But really, it makes things clearer in your code if you go ahead and type out all the column names and also gets rid of any ambiguity as to what is being retrieved. Besides, it also may save some processing if you do not, in fact, actually need every column in that table). Never worry about saving time by avoiding a little typing now, when it might save you a lot of time later when debugging/updating the code later.

[Joseph Witchard]

So the AS DATE part won't mess up any of the other columns?

<?php

$sql = "SELECT post_id, post_title, posted_by, DATE_FORMAT(posted_when, %Y, %m, %d) AS DATE FROM table";

That would be the correct syntax to get the year, month, and date?

[legendx]

So the AS DATE part won't mess up any of the other columns?

<?php

$sql = "SELECT post_id, post_title, posted_by, DATE_FORMAT(posted_when, %Y, %m, %d) AS DATE FROM table";

That would be the correct syntax to get the year, month, and date?


DATE_FORMAT(`date_submitted`, '%Y/%m/%d') = 2008/10/1
DATE_FORMAT(`date_submitted`, '%Y-%m-%d') = 2008-10-1
DATE_FORMAT(`date_submitted`, '%Y . %m . %d') = 2008 . 10 . 1

[Joseph Witchard]

Thanks everyone!

[NogDog]

So the AS DATE part won't mess up any of the other columns?...

Just try to pick an alias name (the word after "AS") that will not collide with any other of the column names being selected and that is descriptive enough that it's easy to remember.

[Joseph Witchard]

You mean I can just pick virtually any name I want to use instead of the word DATE?

[Shorts]

Yup, you can make it banana_sauce and it would be usable as: $row['banana_sauce']

<?
$sql = mysql_query("SELECT post_id, post_title, posted_by, DATE_FORMAT(posted_when, %Y, %m, %d) AS banana_sauce FROM table LIMIT 1");
$row = mysql_fetch_row($sql);
print $row['banana_sauce']; # prints 2008, 1, 1
?>

[NogDog]

I'd prefer 'chocolate_sauce', myself. ;)

[Shorts]

All this sauce stuff is making me hungry...

And yes, 'chocolate_sauce' > 'banana_sauce'

:]

[Joseph Witchard]

Is there a way to set the timezone you want to use when you pull the date out and format it?