Member Avatar for ramsiva

value is 150916124549 how to change the value to Y-m-d
any body help me

  • 3 Contributors
  • 3 Replies
  • 237 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by almostbob

Dani AI

Generated

Quick diagnosis: that string looks like a compact date/time, not a Unix epoch. "150916124549" parses naturally as ddmmyyHHMMSS → 15/09/16 12:45:49 (so Y-m-d would be 2016-09-15). Treating it as a seconds-since-1970 epoch will produce wrong/implausible dates and can overflow on 32-bit builds — which explains ’s odd result. ’s epoch approach is valid only when the column really contains epoch seconds.

PHP (safe, handles two‑digit year explicitly)

$raw = '150916124549';

if (preg_match('/^(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})$/', $raw, $m)) {
  $day   = intval($m[1]);
  $month = intval($m[2]);
  $yy    = intval($m[3]);
  $year  = ($yy < 70) ? 2000 + $yy : 1900 + $yy; // explicit 2-digit-year rule
  $dt = new DateTime(sprintf('%04d-%02d-%02d %02d:%02d:%02d',
           $year, $month, $day, $m[4], $m[5], $m[6]));
  echo $dt->format('Y-m-d'); // 2016-09-15
}

MySQL (convert in the database)

-- check nonconforming rows
SELECT id, col FROM mytable WHERE col NOT REGEXP '^[0-9]{12}$';

-- create a datetime column and populate it from the varchar format
ALTER TABLE mytable ADD COLUMN dt DATETIME NULL;
UPDATE mytable
  SET dt = STR_TO_DATE(col, '%d%m%y%H%i%S');

-- verify results, then DROP/RENAME the old column if OK

Checklist before changing data: backup the table, run the REGEXP/LENGTH checks to find bad rows, verify two‑digit year mapping (adjust if your data spans 1900s vs 2000s), and decide timezone policy (these strings have no TZ). Storing as DATETIME/TIMESTAMP is recommended for correctness and performance.

Recommended Answers

All 3 Replies

Member Avatar for cereal

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:

Member Avatar for almostbob

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
Member Avatar for 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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.