date from timestamp(14)

[anshul]

I used echo date('l, F d, Y', $row['timestamp']); to get date from timestamp(14) in db

It says 'Tuesday, January 19, 2038' lieu 'Saturday, June 18, 2005'

Why?

[jatar_k]

sounds like a bad timestamp, that's my first guess

[madpenguin2]

Are you sure you have a timestamp field and not a datetime field?

[dreamcatcher]

or try:

date('l, F d, Y', strtotime($row['timestamp']));

[anshul]

Thank you very much for your replies.

I used 'timestamp' data-type of length '14' in MySQL applied now() automatically and accurately to all inputs.
echo date("l, F d, Y", '20050619021942'); returns Tuesday, January 19, 2038
echo date("l, F d, Y", strtotime('20050619021942')); returns nothing
gmdate() lieu date() also behaves same; I'm puzzled; what problem PHP has?

$var = date("l, F d Y");
$var = strtotime($var);
echo date("l, F d, Y", $var);

$var = mktime(date("l, F d, Y"));
echo date("l, F d, Y", $var);

Above returns date correctly. Why I use PHP to construct and store time when MySQL do itself correctly?
If also compared outputs of mktime() of PHP and now() of MySQL; mktime() returns something unintelligible to me, now() output resembles in yyyymmddhhmmss format.

$var = "20050619021942";
$var = strtotime($var);
echo date("l, F d, Y", $var);
Above displays nothing. Why?

[davelms]

Check out the PHP function spec for date() - www.php.net/manual/en/function.date.php

The format required by date() is that returned from the function time(), it is something like seconds since 01/01/1970. That's why the version you are attempting to use, or save, ie YYYYMMDDHHMMII, does not work with it.

You're just using the wrong format. If you review the php manual pages it gives you this advice, it also offers you details of timestamp conversion routines, and a link through to the applicable MySQL pages.

[anshul]

Ya, we need write code manually as PHP has no function to read MySQL timestamp of length 14 created by MySQL now().

[g1smd]

>> 'Tuesday, January 19, 2038' <<

That date is the Y2K38 rollover date, the date when it rolls back to zero again (can't remember if that resets the date back to 1900 or 1970 at the moment).

Are you preparing for that yet? or have you got your head in the sand like everyone had for the first rollover problem just over five years ago, when 1999 rolled over to 1900 instead of 2000?

[madpenguin2]

or... you could use the unix_timestamp() function in MySQL to pull out a 'real' unix timestamp. This makes the timestamp compatible with php.

Brett

[gliff]

As others have said, the mysql TIMESTAMP format is different from the format the PHP date() function wants. Use mysql's UNIX_TIMESTAMP function to convert mysql's TIMESTAMP into a UNIX style timestamp (which is what the date() function wants)

SELECT UNIX_TIMESTAMP(fld_timestamp) as fld_unixTimestamp FROM tbl_example

Also, remember that mysql has a date_format() function as well.

[Burner]

You could also let MySQL properly format the date for you instead of formatting it yourself

$sql = "select DATE_FORMAT(your_time_field, '%W, %M %d, %Y') AS time FROM your_table";

Burner

[anshul]

Great Burner.
Your post is perfect!
$query = "SELECT DATE_FORMAT(timestamp, '%W, %M %d, %Y') AS time FROM test";

Continuing with DATE/TIME deal:
Can the experts here, tell us ( tips ) how to deal with local and server times. Most Web pages, I see, display wrong time.

[anshul]

For example, my previous post you display posted at 5:27 am on June 21, 2005 (utc 0)

But I clicked 'Submit' @ 10:58 AM on June 21, 2005

[Burner]

Once you start worrying about timezones, the whole situation gets a lot stickier and a bigger pain to code. Personally I convert everything to Unix timestamps first, then use Greenwich Mean Time (GMT) as a base. You would then need to give the users a way to choose their timezones and then in your code add/subtract the hours for their timezone...

For example, I'm in Eastern Time zone (EST) which is -5.00 hours from GMT. Somewhere I'll save in MySQL that I'm in EST and my code would calculate -5 hours from GMT zone to display to me the correct time.

[php.net...]
[php.net...]

These two links will become your best friend here...

Burner

[Burner]

Here's an example of GMT right now:

$now = gmdate("M d Y H:i:s", mktime(0, 0, 0, date("m"), date("j"), date("Y")));

[anshul]

Above is displaying Jun 20 2005 18:30:00 even if I refresh my page. Local time here is Tuesday, June 21, 2005 at 1:46:03 PM

They did excellent work timeanddate dot com

[anshul]

Date/Time for India is easy ( users note php.net helped ):

$timezone = 5.5; //GMT +5:30 (Delhi, Mumbai, Kolkata, Bangalore)
echo gmdate("l, F d, Y H:i:s", time() + 3600*($timezone+date("I")));

Some countries are using Daylight Saving Time ( DST ), then GMT method goes wrong ;)

[g1smd]

The term "GMT" was made obsolete in 1971. Today, GMT is the UK time zone used only in the Winter (BST -- British Summer Time -- is used in the Summer).

The base reference is UTC and you are well advised to use that instead. See also International Standard: ISO 8601, as well as RFC 3339, and related documents.

[Burner]

You get to be a creature of habit... The reason I use mktime in my code is to make calculation easier for me. For example

$timezone_offset=-5; //I'm in EST
$now = gmdate("M d Y H:i:s", mktime($hour+$timezone_offset, $minute, $second, $month, $day, $year));

Now if I need to calculate the time for a timezone say 1 month from today it becomes:

$timezone_offset=-5; //I'm in EST
$then = gmdate("M d Y H:i:s", mktime($hour+$timezone_offset, $minute, $second, $month+1, $day, $year));

But like I say, I'm a creature of habit so there may be a more efficient way to do it...

Burner

PS if you paste that code from above make sure you populate the vars $year, $month, $day etc...

[anshul]

Many Web pages I read "UTC is also known as GMT, or Greenwich Mean Time"
If you are using Windows, we see 'Time Zone' in 'Date and Time Properties' which provides GMT +/- adjustments all over the world. That is enough, I suppose?
What's UTC vs GMT? Another term to boggle us is 'daylight saving time'! I'm afraid to do all that research, so I use GMT as provided by Windows, I suppose ( until UTC? ).

Just for ideal: [timeanddate.com...]

[edited by: jatar_k at 3:41 pm (utc) on June 22, 2005]
[edit reason] lets not do that thank you [/edit]

[anshul]

From same site I mentioned, I read "India Time Zone UTC + 9 hours"
But then do we need PHP function utcdate()? lieu gmdate() ..!

[edited by: jatar_k at 3:49 pm (utc) on June 22, 2005]
[edit reason] again [/edit]

[g1smd]

GMT is a solar-derived Time Zone but was declared as obsolete in 1971.

UTC is derived from Atomic Clocks, and is kept in step with the Sun by the addition of leap seconds (refer to ISO 8601 for more).

A clock showing GMT will show the same time as one showing UTC, but UTC is the correct designation to use to describe that time.