ISO 8601 is the ISO standard format for timestamps. It looks like “2013-08-27T10:30:00Z”, where the “T” is a separator between the date and time, and the “Z” stands for the UTC timezone.
It’s not often used in end user visible text, but it’s a decent format to use for data interchange, metadata and microformats. As just one example timeago.js uses it to render HTML5-style
If we’re pulling timestamps from PostgreSQL then we can use to_char() to render them in whatever format we like. There’s not (as of 9.3) any canned format for ISO standard format, so we’ll need to use a format string.
A first attempt at that looks like this:
to_char(created at time zone 'UTC', 'YYYY-MM-DDTHH24:MI:SSZ')
created is a “timestamp with time zone” field, as most timestamps in PostgreSQL should be. By default that will be rendered in the current time zone and we’d prefer it be rendered as UTC - so the first thing we do is to convert it to a “timestamp without time zone” type in time zone UTC. Then we pass it to to_char() to format it.
This doesn’t work, though. I’m expecting 2013-08-27T10:30:00Z but what I
end up with is 2013-08-27THH24:30:00Z.
That’s because the to_char format string accepts “TH” to render a number as an ordinal number suffix, e.g. 1ST, 2ND or 3RD. So it’s seeing the TH as a template pattern, then passing the H24 following it as literal text. What I want, though, is to treat the “T” as literal text, then HH24 as a template pattern for the hours in 24 hour format.
The fix is simple enough - put double quotes around the (non-punctuation) characters I want to emit as literal text.
to_char(created at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
That works nicely, and lets me do all the rendering work I need as part of my SQL query and pass the results directly to my HTML engine.
select
id,
created,
username,
memo,
to_char(created at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as stamp
from rule_save
order by created desc;
<table>
<thead>
<tr><th>Ruleset</th><th>Created</th><th></th><th></th></tr>
</thead>
<tbody>
<!--% for i=1,#saved do rule=saved[i] %-->
<tr data-item-id="{< rule.id >}">
<td><a href="{< url('ruleeditor/#rules?load=' .. rule.id) >}">{{ rule.memo }}</a></td>
<td><time class="timeago" datetime="{{ rule.stamp }}">{{ rule.created }}</time></td>
</tr>
<!--% end %-->
</tbody>
</table>