Jump to content


Photo

timestamp format


  • Please log in to reply
4 replies to this topic

#1 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 25 September 2005 - 10:57 PM

I have a problem with the timestamp fields.

In my offline (test) database the timestamp field is formatted in YYYY-MM-DD HH:MM:SS (default 0000-00-00 00:00:00)

when I look at the phpmyadmin online I see that the timestamp fields are formatted like YYYYMMDDHHMMSS (default 00000000000000)

i just looked at the database version in phpmyadmin and my offline (test) version is
Welcome to phpMyAdmin 2.5.7-pl1
MySQL 4.1.9-max running on localhost

and the online version is
Welcome to phpMyAdmin 2.5.6
MySQL 4.0.23-standard running on localhost

Is the formatting of the timestamp field an option you can set in the database or did They just change the format from one version to the other ?

This is causing some weird problems.
when I try to format my timestamp date into a human readable form with
$postdate = date('l dS F Y', strtotime($Row['HomepagePostDate']));
it works on my offline (test) version but not in my online version.
when I look at the values stored in the online version they look correct
value in the database 20050925184204 (yesterday)
value displayed on website Wednesday 31st December 1969

any clever ideas how to solve this ?
anatak
takasi.8008@docomo.ne.jp
tourokum@0508.jp

#2 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 26 September 2005 - 01:45 AM

Just to make sure, are your timestamp fields of type timestamp (as opposed to datetime)?
Cook

#3 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 26 September 2005 - 08:57 AM

yes they are both of timestamp but there is a small difference
this is the offline version (test)
HomepageUpdateDate timestamp Yes CURRENT_TIMESTAMP HomepagePostDate timestamp Yes 0000-00-00 00:00:00

this is the online version
HomepageUpdateDate timestamp(14) Yes NULL
HomepagePostDate timestamp(14) Yes 00000000000000

so in the online version is says timestamp(14) while in the test version it says timestamp
note also that in the onine version the default for the first timestamp NULL is and in the offline (test) version it is CURRENT_TIMESTAMP

but I still wonder why this does not work
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
$postdate = date(\'l dS F Y\', strtotime($Row[\'HomepagePostDate\']))
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

or are there other functions to format a timestamp date to a human readable format ?(except for writing the function myself off course)

kind regards
anatak
takasi.8008@docomo.ne.jp
tourokum@0508.jp

#4 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 26 September 2005 - 09:05 AM

You could use MySQL's date formatting functions instead of PHP's as a workaround. I don't have my reference booklet with me here, but you can look it up in the MySQL manual. For my part I prefer to do all date and time manipulations within MySQL as part of my queries, so that I directly get formatted dates and times the way I need it out of my result sets.
Cook

#5 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 26 September 2005 - 11:49 AM

thank you.
I will use the mysql date time functions for this.

date_format($TableName1.ReviewUpdateDate, '%M %D, %Y') as updatedate,

now I only have to figure out the timezone of my server and then try to solve the difference but I saw some really interesting time functions in mysql I was not aware of.

hehe you learn every day and it never stops does it ?

thanks again I ran some tests and I feel fairly sure this will solve my problems.
anatak
takasi.8008@docomo.ne.jp
tourokum@0508.jp




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users