0

how to convert a date/time value received by "json decoding"

2018-01-05T22:58:23.937 (value of $TimeStamp)

to insert into a SQL-Database-Field type of DATETIME

$query = "INSERT INTO database (date1,...) VALUES ($ddate1, ...)

this won't work:

$ddate1 = date_format(new datetime($TimeStamp), 'y.m.d H:i:s');

removing 'T' won't work either

$ddate1 = strtr($TimeStamp,"T"," "); 
$ddate1 = date_format(new datetime($ddate1), 'y.m.d H:i:s');

UPDATE 6.01.2018

$now = date("y.m.d H:i:s"); $TimeStamp = $fgc['result'][$i]["TimeStamp"];  // (result of json)

echo "OK:" .$now .'<br />'; 
echo "nOK: (format of source) sorce" .$TimeStamp .'<br />';
$dt1 = date('Y.m.d H:i:s', strtotime($Timestamp)); echo "convert:" .$dt1 .'<br />';
$dt2 = date('Y-m-d', strtotime($Timestamp)); echo "convert:" .$dt2 .'<br />';

Result:

OK:

18.01.06 12:48:39

(sample stored correcrtly in database)

nOK: (format of source) sorce2018-01-06T11:48:40.207

convert:1970.01.01 01:00:00

convert:1970-01-01

thanks for any hints

Stefan

ROOT
  • 11,363
  • 5
  • 30
  • 45
Stefan S.
  • 39
  • 7
  • Possible duplicate of [Convert JS date time to MySQL datetime](https://stackoverflow.com/questions/5129624/convert-js-date-time-to-mysql-datetime) – csabinho Jan 05 '18 at 23:27
  • You need to quote the date in the INSERT statement, as it will be passed as a string literal: `"INSERT INTO database (date1,...) VALUES ('$ddate1', ...)`. – trincot Jan 06 '18 at 12:03
  • it is quoted $query = "INSERT INTO kurse ( Zeitstempel, DatumUpdate, ... ) VALUES ( '$now', '$dt1', ...) – Stefan S. Jan 06 '18 at 12:12
  • I'm voting to close this question as off-topic because it was caused by **a problem that can no longer be reproduced** (confirmed in [Stefan S.'s answer](//stackoverflow.com/a/48127768) below. – robinCTS Jan 07 '18 at 14:07
  • yes, thanks, can be closed ... do I ave to close the question? I can't see how/where? – Stefan S. Jan 13 '18 at 12:39

3 Answers3

1

This should do the trick:

$date = '2018-01-05T22:58:23.937';
echo date('Y-m-d', strtotime($date)); // 2018-01-05

Alternatively, you can use OOP style DateTime class as follows:

$dt = new DateTime('2018-01-05T22:58:23.937');
echo $dt->format('Y-m-d');
Tommaso Belluzzo
  • 23,232
  • 8
  • 74
  • 98
  • Thank you ... problem solved partly .. at least new record is generated but value in database is set to: 0000-00-00 00:00:00 $dt = date('y.m.d H:i:s', strtotime($dt)); also is set to 0000-.... i guess I am close to the solution ... – Stefan S. Jan 05 '18 at 23:49
  • Mh ok... have you made some tests to see what is the current PHP time offset (`date_default_timezone_get`) and the current MySQL time offset? What is your MySQL defaut date format? – Tommaso Belluzzo Jan 06 '18 at 01:39
  • echo "OK:" .$now .'
    '; echo "nOK: (format of source) sorce" .$TimeStamp .'
    '; $dt1 = date('Y.m.d H:i:s', strtotime($Timestamp)); echo "convert:" .$dt1 .'
    '; $dt2 = date('Y-m-d', strtotime($Timestamp)); echo "convert:" .$dt2 .'
    ';     – Stefan S. Jan 06 '18 at 11:50
0

problem solved (just the same coding as in my first post ... must have been an different error ... sorry!)

$TimeStamp = $fgc['result'][$i]["TimeStamp"];

converted by

$DatumUpdate = date_format(new datetime($TimeStamp ), 'y.m.d H:i:s');

stored correctly in database

Stefan S.
  • 39
  • 7
0

make a function like blew what u need im not coded just exple it u need make it wt u need..

    function shortDu($t) {
$sam = str_replace('PT','',$t);
$sam = str_replace('H',':',$sam);
$sam = str_replace('M',':',$sam);
$sam = str_replace('S','',$sam);
return $sam;}

apply this shortDu(); on your current value then get perfect result then input in db

Ananya Sharma
  • 67
  • 5