CODEWITHSUNDEEP
phpmysqldatabasetimestamp
bzupnick
bzupnick

Reputation: 2814

How do i retrieve and display a timestamp from a mysql database using PHP?

Currently I'm using this line of code

echo date("F j Y g:i:s", $row[date]);

But it just gives me January 1 1970 2:33:31

I also want it to look normal because if I don't do the date("F j Y g:i:s", at all, all I get is 2011-03-02 23:00:30 which is the correct date, but displayed in a very abnormal way

Upvotes: 0

Views: 7015

Answers (2)

GolezTrol
GolezTrol

Reputation: 116200

Typecast to UNIX_TIMESTAMP inside the query:

SELECT UNIX_TIMESTAMP(YourDateField) FROM YourTable

Since I see responses to the other answer while this is ignored, let me elaborate:

There are two common date types. Both are actually numbers, that represent a duration since a given date.

  1. One is the number of days (float) since 1900. One is one day. The fraction is the fraction of the day.
  2. Other is the number of seconds since 1970. This can be an int (whole seconds) or a float (including fractional seconds).

If you got the first date, but treat it like the second format, you arecounting days for seconds. Instead of 111 years since 1900, you're counting 111 seconds since 1970. That explains why you get that date.

Therefore, use the UNIX_TIMESTAMP function, which will convert the first float notation to a timestamp in seconds. It is needed because that is also the type PHP uses.

Upvotes: 5

Michael
Michael

Reputation: 1231

You probably need to use strtotime, the date functions expects the 2nd parameter to be a unix timestamp

date("F j Y g:i:s", strtotime($row[date]));

If you want different formatting you can take a look at this page: Date formatting

You might want something like this:

date("Y-m-d H:i:s", strtotime($row[date]));

Upvotes: 3

Related Questions