Forum OpenACS Q&A: Formatting dates / times nicely

Request notifications

Posted by James Harris on

Is there a standard OACS way of formatting dates and times in human readable format? I can do it within SQL but it would seem more logical and cross platform to do it within TCL.

Posted by bill kellerman on
i don't know if there is a built in openacs helper proc, but i use tcl's clock format.

it takes a datetime value (in seconds) and formats it.  if you want to do the opposite and translate a datetime into seconds, use clock scan.

Posted by Claudio Pasolini on
You can also look at the new localization procs in packages/acs-lang/tcl/localization-procs.tcl
Posted by Joel Aufrecht on
As of 5.0, the preferred way to format a date/time is

  lc_time_fmt datetime fmt [ locale ]

where fmt is one of these:
      %c          FDCC-set's appropriate date and time
      %q          Long date without weekday
      %Q          Long date with weekday
      %x          FDCC-set's appropriate date representation.
      %X          FDCC-set's appropriate time representation.

If you use one of these formats, it is automatically localized to the user's selected locale.

Anybody who knows what FDCC stands for is welcome to chime in.

Posted by Tom Ayles on
Is there any way of obtaining a locale-specific format string to use in DB to_char commands? A lot of the formatting seems to happen with the DB (which is fair enough) but the format string is hard-coded into the query. Over a period of time, it might be better to work these out and replace them with a format string generated by the localisation procs.
Posted by Lars Pind on

Code that does pretty-formatting in the DB should be changed to do so in the Tcl layer.

Here's the recommended way of doing things:

db_multirow -extend { mydate_pretty } {
    select to_char(mydate, 'YYYY-MM-DD HH24:MI:SS') as mydate_ansi,
} {
    set mydate_ansi [lc_time_system_to_conn $mydate_ansi]
    set mydate_pretty [lc_time_fmt $mydate_ansi "%x %X"]

... or use your desired date/time format as the second argument to lc_time_fmt.

The only date format we should ever get out of the DB is ANSI dates.


Posted by Jade Rubick on
I've added this to the FAQ. (
Posted by Joel Aufrecht on
Yeah, well, I added it to a new section in the Engineering standards called "Variables," so there :)

Jade, didn't you have some other new coding standard stuff that should go into the docs?

Posted by Don Baccus on
Joel ... there are so many time/date issues to deal with when you add them up (Oracle vs. PG, localization) that perhaps there should be a section that addresses all such issues coherently in one place?

It's one of those areas where even experienced OpenACS hackers need to refer to at times because the arcaneness involved makes it hard to remember it all.

Posted by Mark Aufflick on
far out - where did those faq's come from? Jade you are a documentation machine!! :D
Posted by Randy O'Meara on
Now, if the FAQs were just searchable...