Forum OpenACS Q&A: formatting timestamps

What is the "best practices" way of formatting PostgreSQL timestamps?

I tried util_AnsiDatetoPrettydate but it drops the time field.

I tried dt_ansi_to_pretty but I get an error like this:

  unable to convert date-time string "2002-04-25 11:22:00+08"  while executing clock scan $ansi_date  (dt_ansi_to_pretty line 6)  ...  

I worked around this by splitting the PostgreSQL date into a date component and a time component:

  select cast(start_timestamp as date) as start_date, cast(start_timestamp as time)...  

Then I use

  set start_time_str [dt_ansi_to_pretty $start_date]  append start_time_str " $start_time"  

Is this a bug in dt_ansi_to_pretty? Or is there something wrong with the way I get my date from PostgreSQL?

Reformat the date outside of the database is silly. Not sure why that was the ACS approach, because it's twice as much work as just having the DB spit out the format you want in the first place. (First you have to parse the db's format, then reformat.)

I wanted to have my timestamps displayed with just the hour/minute info if it was today's date, give the full date. And I want the timezone, since I have a lot of non-US users. I couldn't do that at all with the existing tcl functions, and as I said, trying to extend them to do that is IMO the wrong approach. Here's the PG function I use instead:

drop function pretty_timestamp (timestamp);
create function pretty_timestamp (timestamp) returns varchar as '
    spi_exec "select to_char(''$1''::timestamp, ''Month DD YYYY HH:MI AM TZ'') as s"
    set s_date [string range $s 0 16] ;# month is padded so it's always this long
    spi_exec "select to_char(now(), ''Month DD YYYY'') as now_date"

    #todo: trim zeros from hours (but not minutes)

    if ![string compare $s_date $now_date] {
        # same day; just return time
        return [string range $s 18 end]
    }
    # different day; return date + time
    regsub -all " +" $s " " s ;# rm space padding from month name
    set s_list [split $s]
    set trimmed_day [string trim [lindex $s_list 1] "0"]
    return [join [lreplace $s_list 1 1 $trimmed_day]]
' LANGUAGE 'pltcl'
;

Don, Jonathan,

Thanks for the guidance.

On a related note...
The documentation for the dt_* procs are included in the API documentation, but there is too little HOW in the API docs,
and almost no WHY.

For example, there's no "reformatting the date outside the
database is silly" in the documentation. I'm thinking of something
like the classic book Peter Norton's PC Progremmer's Solver.

Here's my first stab at it:
http://www.geocities.com/radam_avatar/oacs-cookbook.html.

Are other people working on something like this? Let me know so that I can just contribute to their efforts.

Again, thanks for the tips!

Jonathan, If you want to remove the padding from, let's say, month and hour it can be done with the "FM" prefix:
<br><br>
spi_exec "select to_char(''$1''::timestamp, ''<b>FM</b>Month DD YYYY <b>FM</b>HH:MI AM TZ'') as s"
<br><br>
This works in PG 7.1.3 and beyond. (Maybe before that too)...

To enable internationalisation I presume the thing to do is pass in the format string as a variable. There might be problems with the object creation pl/pgsql functions which have already run up against the 16 parameter limit.

It's also a pain in a query which you want sorted by date, where the textual representation of that date sorts in some other order.

Wouldn't it be nice to have a few centrally defined pl/sql functions that return some frequently used date formats, instead of repeatedly using to_char? This would also be a step  towards internationalization of the toolkit. For example for a german site I find myself rewriting a lot of to_char(some_date, 'MM/DD/YYYY HH12:MI PM') to to_char(some_date, 'DD.MM.YYYY HH24:MI'), while it would be much nicer to just change a function like oacs_dt_short in only one place.

I am sure this has been discussed before, but I couldn't find where.