How to get date_format() to work properly

[kuau]

I am trying to do a very simple thing but I am getting erratic results. I believe I am doing it exactly as defined in the php manual, but it is not working. In the php manual a user, James Meyer, commented that php 5.2 and 5.3+ have issues with this command. So what works?

All I want to do is leave the time off a datetime value when displayed on a webpage. I have tried every possible way I can imagine using both date() and date_format(), but I either get all the dates as 1969-12-31 or nothing at all. I use an SQL command to retrieve the values from the database and then this php to display them...


<?php echo date_format($client['edited'], 'Y-m-d');?>

If I just use <?php echo $client['edited'];?> the date displays the correct date but with the time also, which wraps and messes up the display.

I just noticed this message in the error log... in the SQL command I use $client = mysql_fetch_assoc($result) and do not convert the value from the DateTime field...


PHP Warning: date_format() expects parameter 1 to be DateTime, string given in /public_html/php/file-name.php on line 20

[keyboard]

What is the value of $client['edited'] (could you give an example)
I thought to be able to use date ( and I assume date_format ) too, the parameter has to be a time stamp ( time() ).

[kuau]

The field `edited` is a Timestamp datatype, for example 2012-01-06 18:26:16. It registers the last time the record was edited.

I just want to display 2012-01-06 without the 18:26:16. Simple you would think.

[keyboard]

Try


echo date("Y-m-d", $client['edited']);

If $client['edited'] is a timestamp (eg. 1337943587) it should work...

[kuau]

OK, I tried that and nothing displayed but I got this error message...


PHP Warning: date_format() expects parameter 1 to be DateTime, string given in /public_html/php/client-listing.php on line 20


It's very strange because the table column is defined "edited timestamp" yet it displays in phpMyAdmin as 2012-01-06 18:26:16 and won't take commands.

So I put it back to <?php echo $client['edited'];?> and now it displays the proper date and wraps again. Do you think it is a bug in this version of php?

[ApacheTech]

Are you using:


echo date("Y-m-d", $client['edited']);

or



echo date_format("Y-m-d", $client['edited']);

The former is correct.

[kuau]

I tried both. Neither worked.

[keyboard]

date() and date_time() both need datetime objects as their parameters (eg. 1337943587) while you're using sql timestamps - (eg. 2012-01-06 18:26:16). That's why it is producing that error when you try and use date/date_format.

I don't use sql timestamps (I find them annoying) (I use unix time instead ( time() ) so I'm not sure how to answer this one.

The only thing I can suggest, is google convert sql timestamps to unix timestamps, then running the code you already had...

I haven't really looked at it, but this (http://mysql.databases.aspfaq.com/how-do-i-convert-a-sql-server-datetime-value-to-a-unix-timestamp.html) might be what you're looking for.

[traq]

Actually, the mysql DATETIME format -while needing some "getting used to"- is preferred, since it is more flexible - it's more easily translated to PHP's internal date/time format (64bit int). It also allows a greater date range than unix timestamps (about 292 billion years past/future, compared to the unix timestamps, which will run out of numbers in 2038).

It also allows you to do manipulations on the date via MySQL (i.e., as part of the query instead of afterwards in PHP, reducing overhead).

But, for your purposes, either will work.


<?php
// functional approach:
print date( "Y-m-d",strtotime( $client['edited'] ) );

// object-oriented approach:
$date = new DateTime( $client['edited'] );
print $date->format( "Y-m-d" );

// (both result in the same output.)

[kuau]

Dear traq:

I tried what you suggested. In fact, that was the very first thing I tried thinking it would work, but wherever the record had not yet been edited, ie. the date is 0000-00-00, it would display the date as -0001-11-30. I even tried adding an if($edited == '0000-00-00'){ etc. which didn't help.

I know this should be a simple thing but nothing seems to work as expected. Here is my code which displays 0000-00-00 as -0001-11-30...


<?php echo date( "Y-m-d",strtotime($client['edited']));?></div>

Have you tried it yourself?

[traq]

Are you pulling the values from your DB?

MySQL defaults to 0000-00-00 when it can't understand a given date. Likewise, strtotime() defaults to 1969-12-31 when it doesn't understand a date. (I don't know why you're getting "-0001-11-30"; I couldn't reproduce that.)
<?php

// are you _sure_ that $client['edited'] === '0000-00-00' ...?
// try passing '0000-00-00' directly ...?

echo date( "Y-m-d",strtotime( '0000-00-00' ) );
// output: 1969-12-31

echo date( "Y-m-d",strtotime( '2012-05-30' ) );
// output: 2012-05-30

[kuau]

Yes, I am pulling the values from a mySQL database. When the record has never been edited, the `edited` field is NULL. It displays as 0000-00-00 00:00:00. I am sure about that. When I use echo $client['edited'] with no formatting, that is what I get.

[traq]

odd - I still get 1969-12-31, even with the 00:00:00 part. That is the correct behavior, according to php.net. (MySQL giving "0000-00-00 00:00:00" for NULL is also the correct behavior.)

You said you did a conditional (if) check on the $client['edited']; what did that look like?
Something like this should be sufficient...
<?php

if( $client['edited'] == '0000-00-00 00:00:00' ){
print "this entry has never been edited.";
// or set it to today's date, or whatever
}

[kuau]

Dear traq: I'll try that again, but right now I thought I would try one of your earlier suggestions about doing the formatting in the SQL command. BTW, is it true that if you wish to specify one field that you have to name them all? I went from SELECT * to a huge list of columns.

Anyway, what I did is displaying as blank. I must be doing something else wrong now... oh, I figured it out... here is what works...


SELECT `client_id`, `agent`, `lastname`, `firstname`, ...etc... , date_format(`edited`,'%Y-%m-%d') AS `edited` FROM client

Then in the report: echo $client['edited']

It works! Yahoo! Thanks for all your help. :)

[traq]

BTW, is it true that if you wish to specify one field that you have to name them all? I went from SELECT * to a huge list of columns. ...
I'm glad to hear you got it sorted out.

If I understand your question, no, you don't *have* to list all the columns - you can do like:
SELECT *,date_format(`edited`,'%Y-%m-%d') AS `edited_formatted`FROM client ...While it's easier, I don't think it's "better," per se. But it does work (note that you can't use one of the actual column names if you do this -as `edited`won't work; as `edited_something` will- mysql will complain if there's a column name conflict).

[kuau]

Dear traq: Yes, that is exactly what I wanted to know (SELECT *,date_format...). Thank you.

The funny thing is that mysql didn't complain when I used an actual column name after AS. I did it successfully yesterday too when I had to re-run a script on a different column name so it saved me having to edit the second script which was great. I hope it is OK to keep doing it (?).

My report looks so good now! :)

[traq]

if you use *, it will become a problem: e.g.,

# this works just fine, as long as you don't also SELECT `example`
SELECT `one`,`two`,`three`,DATE_FORMAT( `example`,'%Y-%m-%d' ) as `example` ...

# however, this will cause an error:
SELECT *,DATE_FORMAT( `example`,'%Y-%m-%d' ) as `example` ...
# because you're trying to use `example` as an alias,
# when you've already selected a column with that name (via *).
# MySQL doesn't like the ambiguity.

SELECT *,DATE_FORMAT( `example`,'%Y-%m-%d' ) as `no_col_has_this_name` ...
# working fine again.

[kuau]

Dear traq: I just got back home so I didn't see this until now. I had already changed it this afternoon to...


$sql = "SELECT *, date_format(`edited`,'%Y-%m-%d') AS `edited` FROM client

That is my exact code. I swear to god it worked fine right off the bat and I just checked again and it is working perfectly. Maybe my version of mysql is more forgiving than yours. Thanks for solving my problem for me. Now if I can just get the array one solved, I'll be happy. :)

[traq]

it worked fine right off the bat and I just checked again and it is working perfectly. ...

How very strange... I've actually tried this, and I get an error. I'm glad it's working, though!

[kuau]

Yeah, isn't it ironic that what the manual says will work doesn't work, but what shouldn't work, does. Enough to make you crazy... :)