5

I have a mySQL with a table. There are 30 records all with a date column.

How do I change all my existing records in my table to have today's date with is format?

date_default_timezone_set('America/Los_Angeles');
$date = date("m/d/y g:i A") ;
Book Of Zeus
  • 49,509
  • 18
  • 174
  • 171
Erik
  • 5,701
  • 27
  • 70
  • 119
  • varchar(20) is the definition – Erik Sep 25 '11 at 21:31
  • 2
    I don't recommend to use varchar as date, use timestamp or datetime, then you can format mysql date using: DATE_FORMAT() – Book Of Zeus Sep 25 '11 at 21:32
  • ok, but how do I change the existing records? – Erik Sep 25 '11 at 21:32
  • If you're building an application that should last a while, consider storing times in GMT/UTC instead. It's an accepted practice, even if your audience will never be in that time zone. It's a bit more complicated to handle though because you have to do calculations when outputting dates and times. – Pekka Sep 25 '11 at 21:34
  • Related reading: [Daylight saving time and Timezone best practices](http://stackoverflow.com/q/2532729) it's not necessary to delve into this if it's just for a hobby site - in that case, stick with PST. But for anything bigger, it's worth doing properly from the start. – Pekka Sep 25 '11 at 21:37

1 Answers1

7

Here's the fix for the VARCHAR to DATETIME (this will erease the current value):

ALTER TABLE mytable modify column `mycolumn` datetime NOT NULL DEFAULT 0;
UPDATE mytable SET mycolumn = NOW() WHERE ...;

or

UPDATE mytable SET mycolumn = '2011-09-25 17:40:00' WHERE ...;

If you want to save the current value use:

ALTER TABLE mytable add column `newdate` datetime NOT NULL DEFAULT 0;
UPDATE mytable SET newdate = mycolumn;
ALTER TABLE mytable DROP COLUMN mycolumn;

If you want to select the date in the format you can:

SELECT DATE_FORMAT(mycolumn, '%m/%e/%y %h:%i %p') FROM mytable WHERE ...

Or in your PHP you can use:

date_default_timezone_set('America/Los_Angeles');

// query select ($row = mysql_fetch_assoc($query)...

$date = $date = date("m/d/y g:i A", strtotime($row['mycolumn']));
Book Of Zeus
  • 49,509
  • 18
  • 174
  • 171