Menu
I have a MySQL DB table with a column named "timestamp", a type of timestamp, and attribute of on update CURRENT_TIMESTAMP and a default of CURRENT_TIMESTAMP.
If I add a record to the table, specifying other values, but not the timestamp, then the timestamp is automatically added like 2016-12-28 17:02:26.
In PHP I query the table using the following query
SELECT * FROM history WHERE user_id = 9 ORDER BY timestamp ASC
The result of the query is saved into $rows and I use a foreach to create an array with some of the other values formatted. I am attempting to format the time stamp to UK type 24-hour date time: dd/mm/yy, HH:MM:SS.
I have tried both the date() and strftime() functions as follows:
$formatted_datetime = strftime("%d %m %y %H %M %S", $row["timestamp"]);
$formatted_datetime = date("d/m/y, H:i:s", $row["timestamp"]);
Both of these result in the following notice and the date time being output incorrectly like 01 01 70 00 33 36:
Notice: A non well formed numeric value encountered in /home/ubuntu/workspace/pset7/public/history.php on line 20
I am new to PHP and MySQL and so far none of the other questions or documentation I have seen have successfully addressed performing this conversion.I do not understand why strftime() does not work, nor how to do this properly?
792
The line $phpdate = strtotime( $mysqldate ) accepts a string and performs a series of heuristics to turn that string into a unix timestamp. The line $mysqldate = date( 'Y-m-d H:i:s', $phpdate ) uses that timestamp and PHP's date function to turn that timestamp back into MySQL's standard date format. Save this answer.
Solution: This can be achieved with the help of date() function, which is an inbuilt function in PHP can be used to format the timestamp given by time() function. This function returns a string formatted according to the given format string using the given integer timestamp or the current time if no timestamp is given.
Example: For converting datetime to format – dd:mm:yyyy, you can use the following query: SELECT DATE_FORMAT('2020-03-15 07:10:56.123', '%d:%m:%Y');
To do this the OO (and most flexible) way use DateTime class and use the static createFromFormat method to instantiate a new DateTime object:
$new_datetime = DateTime::createFromFormat ( "Y-m-d H:i:s", $row["timestamp"] );
Now you can use the $new_datetime object to generate any string representation you'd like by calling the object's format method:
echo $new_datetime->format('d/m/y, H:i:s');
To boot, you since you've a DateTime object you can now also to any manner of transformation (like shifting timezones or adding days), comparison (greater or less than another DateTime), and various time calculations (how many days/months/etc... between this and another DateTime).
DateTime:http://php.net/manual/en/class.datetime.php Learn it. Love it. Live it.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
