3 Years
Discussion Span
Last Post by almostbob

Hi, if that is a unix timestamp then do:

-- updating date format
UPDATE `table_name` SET `col_name` = FROM_UNIXTIME(`col_name`);

-- changing colum type to datetime
ALTER TABLE `table_name` MODIFY `col_name` datetime null;

If you want to preserve the old value, then add a new column and update against it:

ALTER TABLE `table_name` ADD `new_col_name` datetime null;
UPDATE `table_name` SET `new_col_name` = FROM_UNIXTIME(`col_name`);

More information here:


echoing cereal
update the table to store the date as a date object, timestamp occupies 4 bytes/record important when you have 100 million records
date objects are designed for datetime use
select is simply numeric (orders of magnitude faster)
date object can be parsed to the text format required by the user on output, or from the text format required by the user on input, input fields for date have a standardised form
very few people, as a %age of the world, recognise any single text date form

if you just want the date from existing data

echo date("Y-m-d" , $date); 

where $date is the value returned from the db

Edited by almostbob

<?php date_default_timezone_set('America/Halifax'); 
echo '150916124549  = '.date("Y-m-d" , 150916124549); ?> 

150916124549 = 1988-10-07

I get bored easily

Edited by almostbob

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.