Converting date/timestamp to human form.

Anon

I have a database that I am running queries against and dynamically generating php pages with. My question is, one of the fields (L_DATE) contains a date/timestamp courtesy of Mysql. My question is, I need to grab that field (along with about 8 others) and convert the date to a human form then display both the date and the data returned.

So far none of the date related issues I have found match my problem well enough for me to figure out how to convert it.

Any help is appreciated.

Anon

Try using the date function, with the first parameter being a string to specify the format. So if your timestamp from the database is $result["timestamp"], then do:

date("m/d/Y", $result["timestamp"]);

to return the date in that format. For other options, refer to the documentation on the date function on php.net.

-Jeremy

Anon

that rocked nads, but now all my date's are:

12/31/1969

Any reason why this happened?

cordex

I don't believe the MySQL timestamp works in the date function. Different system altogether.

You need to use UNIX_TIMESTAMP when calling your timestamp field.

"SELECT UNIX_TIMESTAMP(insert timestamp column here) FROM table"

Anon

That happened because the value of the field is either 0 or is being interpreted as 0. The Unix timestamp begins at midnight on January 1, 1970, and so 0 seconds resolves to just before it on 12/31/69.

In response to Ben, I believe the two formats are compatible, since a Unix timestamp is a Unix timestamp. The PHP function time() and the MySQL function UNIX_TIMESTAMP() should theoretically return the same value if run at the same time.

-Jeremy

cordex

In response to Ben, I believe the two formats are compatible, since a Unix
timestamp is a Unix timestamp.
The PHP function time() and the MySQL function UNIX_TIMESTAMP() should
theoretically return the same value if run at the same time.

Isn't that just what I said? Yes, a UNIX timestamp is a UNIX timestamp is a UNIX_TIMESTAMP() (usually). But how do you know he was using a UNIX_TIMESTAMP()? He specified a mysql timestamp and at no point did he say he was converting to UNIX. I wasn't sure if his "timestamp" was being converted to unix format (called via UNIX_TIMESTAMP - seconds since epoch) or mysql timestamp format (YearMonthDayHourMinSec).

I suspect that his errors have been caused because he wasn't retrieving the values via the aforementioned method.

-Ben

Anon

hrmf...apparently I failed to explain the type of datestamp I'm getting in the mysql db. The date format as it is posted to the datetime type field is listed as:

2001-07-20 00:00:00

It's this datestamp that I need converted to human form.

Anon

I didn't mean to start a debate...honest! =]

Anon

Hehe...no debate. In the case that you're using the datetime field you mentioned, just use the UNIX_TIMESTAMP() function in selecting the field's data (as Ben described), and then run the result through the date function (as I described). That field isn't a true timestamp after all, so Ben was correct on that count (even despite the needless vitriolic sarcasm).

-Jeremy

Anon

hrmf...well..doing that I still get the 12/31/69 return. Here's the actual sample code I'm attempting to use:

$sql = "select UNIX_TIMESTAMP(L_DATE) FROM Database WHERE string_entered = '$requireditem' AND extra LIKE '%d%'";

Once the data is grabbed, the output is handled by this snipet:

echo "<td valign=top>".date("m/d/Y",$row["L_DATE"])." </td>";

See anything wrong that would still cause Epoch?

Anon

Try using an AS clause to specify a new name for that field once it's been passed through UNIX_TIMESTAMP().

Like
$sql = "select UNIX_TIMESTAMP(L_DATE) AS something FROM Database WHERE string_entered = '$requireditem' AND extra LIKE '%d%'";

Then use $row["L_DATE"] as your var.

-Jeremy

Anon

Rock on! I got the correct date now, but one final question before I elevate your status to MySQL/PHP God:

All Null entries in that column still return 12/31/1969. Any way around this?

cordex

You could either check for null values outside your MySQL statement and not parse the same way you do the others or you could add a WHERE clause to skip over null entries. It depends on whether you want to display entries with null or invalid dates.

-Ben

Anon

Sorry, can't be a God, then... =) There are a number of ways of avoiding that, but none of them are magical. You can set them all (using some unique key of the fields) to a particular value, or you can code the PHP to output something else in the place of nulls. But basically, it's all just a choice of how to avoid output, not a problem to fix.

-Jeremy

Anon

Yah. I guess at this point it becomes a process of maintaining the data in the db itself. (Which at the moment is not my responsibility) I truly appreciate all of your help..it's been beating me down for a few days.