Formatting a MySQL timestamp field

Archived from the Xataface Users forum.

discostrings — Tue Jan 17, 2012 1:51 pm

I’ve just started my first project using Xataface, and so far I’m very impressed!

I have a quick question about formatting dates. I’m sure this should be possible and easy, but I haven’t found the correct way to do it even after some rather extensive searching.

If I use a DATETIME field, dates appear in list view like “2012-01-17 13:04:03”. But if I use a TIMESTAMP field, which I need to do, they appear like “20120117130403”. I need to have the time display formatting that is applied to DATETIME fields also apply to the TIMESTAMP field.

I tried using date_format in fields.ini with no luck.

Is there a correct way to do this?

Thanks!

---

shannah — Tue Jan 17, 2012 2:15 pm

It should handle Timestamp the same way it handles datetime. What version are you using? Can you post the relevant portions of the table structure (e.g. the create table statement) so I can take a look. It sounds like it is just treating it like a normal varchar field.

A workaround is always to define your own fieldname__display() method in the delegate class that formats the value exactly the way you want.

---

discostrings — Tue Jan 17, 2012 4:20 pm

shannah wrote:It should handle Timestamp the same way it handles datetime. What version are you using? Can you post the relevant portions of the table structure (e.g. the create table statement) so I can take a look. It sounds like it is just treating it like a normal varchar field.

A workaround is always to define your own fieldname__display() method in the delegate class that formats the value exactly the way you want.

Thanks Steve for your quick reply–fieldname__display() worked wonderfully.

If you’d like to see whether it’s a Xataface issue, my version is 1.3rc6 and the create statement is:

Code: Select all

CREATE TABLE `testtable` (   `id` int(16) NOT NULL auto_increment,   `created` timestamp NOT NULL default CURRENT_TIMESTAMP,   PRIMARY KEY  (`id`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

I can confirm that every time I try a TIMESTAMP field it displays a string with just numbers. Perhaps the problem is because my version of MySQL is quite dated–5.0.92.

Thanks again–I really enjoy this tool and I’ll be around.