8

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?

Toby
  • 9,696
  • 16
  • 68
  • 132
  • Possible duplicate of [convert mysql timestamp into actual date and time?](http://stackoverflow.com/questions/5547252/convert-mysql-timestamp-into-actual-date-and-time) – TIGER Dec 28 '16 at 17:43
  • 1
    Sidenote: I wouldn't use `TIMESTAMP` due to its limitations: *"The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to **'2038-01-19 03:14:07'** UTC."* https://dev.mysql.com/doc/refman/5.5/en/datetime.html use `DATETIME` *"The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to **'9999-12-31 23:59:59'.**"* – Funk Forty Niner Dec 28 '16 at 17:45
  • @Tiger The selected solution there seems... like a lot. Would I benefit from a different type for the timestamp column on my table? – Toby Dec 28 '16 at 17:45
  • 2
    That 2038 is a UNIX bug http://stackoverflow.com/q/2012589/1415724 - Edit: You deleted the comment I was responding to. – Funk Forty Niner Dec 28 '16 at 17:49
  • @Fred-ii- Sorry, I noticed your "Sidenote:" bit at the start and realised my comment was unnecessary. Thanks though! – Toby Dec 28 '16 at 17:51
  • You're welcome. Yeah, I admit I edited my first comment a few times. But now you know about that UNIX bug; the choice is yours to keep going with that. Just thought I'd let you know about it so you don't get any unpleasant surprises later on. – Funk Forty Niner Dec 28 '16 at 17:52
  • @Fred-ii- In addition to changing to DATETIME I added a trigger to set the default value (my MySQL instance must not be the most recent as it does not allow `CURRENT_TIMESTAMP` as a DATETIME default): `CREATE TRIGGER BEFORE INSERT ON FOR EACH ROW SET NEW. = NOW()` – Toby Dec 28 '16 at 18:15

5 Answers5

10

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.

Ray
  • 40,256
  • 21
  • 101
  • 138
5

Normally in MySQL date timestamp save time as YYYY-MM-DD HH:MM:SS (2016-12-20 18:36:14) formate you can easily convert them as your wish using date formate but have to convert your input to time first. Following will do the trick

$formatted_datetime = date("d/m/y, H:i:s", strtotime($row["timestamp"]));
TIGER
  • 2,864
  • 5
  • 35
  • 45
reza
  • 1,507
  • 2
  • 12
  • 17
3

Why not make MySQL do the work? And do you really want mm/dd/yy, not dd/mm/yy?

SELECT *, DATE_FORMAT(timestamp, '%m/%d/%y, %T') formatted_date FROM ....

Then you can extract the formatted date as $row['formatted_date'].

See https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Giles B
  • 411
  • 4
  • 7
  • Whoops, you are correct, I do want dd/mm/yy, thanks. I'll edit fothwith! – Toby Dec 28 '16 at 18:04
  • I had seens DATE_FORMAT() mentioned but only in relation to `SELECT DATE_FORMAT...` and wasn't sure how to adapt it, I'll give it a try – Toby Dec 28 '16 at 18:06
  • This works and I like the neatness of it, but I think Ray's DateTime object method is a bit more flexible. – Toby Dec 28 '16 at 18:11
  • 1
    This will work to generate the desired output, but moves application logic into MySQL, which is general a bad architectual choice. Reasons not to do this are such as code portability -- changing DB's (to say oracle or postges) may change the functions available, your SQL would then be invalid. – Ray Dec 28 '16 at 18:12
  • Using a DateTime object definitely gets you a more capable object and it is probably more portable (SQL server, for instance, does not appear to have a DATE_FORMAT function, although PostgreSQL does). It's just slower. Which you may well not care about in the circumstances. Cheers. – Giles B Jan 09 '17 at 20:17
2

The date is of timestamp type which has the following format: ‘YYYY-MM-DD HH:MM:SS’ or ‘2008-10-05 21:34:02.’

$res = mysql_query("SELECT date FROM times;");
while ( $row = mysql_fetch_array($res) ) {
   echo $row['date'] . "
";
}

The PHP strtotime function parses the MySQL timestamp into a Unix timestamp which can be utilized for further parsing or formatting in the PHP date function.

Here are some other sample date output formats that may be of practical use:

echo date("F j, Y g:i a", strtotime($row["date"]));                  // October 5, 2008 9:34 pm
echo date("m.d.y", strtotime($row["date"]));                         // 10.05.08
echo date("j, n, Y", strtotime($row["date"]));                       // 5, 10, 2008
echo date("Ymd", strtotime($row["date"]));                           // 20081005
echo date('\i\t \i\s \t\h\e jS \d\a\y.', strtotime($row["date"]));   // It is the 5th day.
echo date("D M j G:i:s T Y", strtotime($row["date"]));               // Sun Oct 5 21:34:02 PST 2008
Santosh Dangare
  • 685
  • 1
  • 4
  • 15
0

I would use the Carbon class and its String Formatting

$carbon = Carbon::instance('2016-12-28 17:02:26');

Then you can format it the way you want.

Samuel
  • 781
  • 5
  • 22