timestamp format

[anatak]

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

[Cook]

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

[anatak]

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

[Cook]

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.

[anatak]

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