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'
;
