Re: Padding time values from EXTRACT() - Mailing list pgsql-novice

Mark Kelly wrote:
> ...
> EXTRACT (hour FROM timestamp) || ':' ||
> EXTRACT (minute FROM timestamp)
> AS this_time
>
> I get single digit figures returned for minutes less than 10, resulting in the
> odd-looking 12:5 when I was expecting 12:05.
>
> Actual column value is '2009-11-18 12:05:31.030546', which I why I assumed I'd
> get the 0 in the returned value.
>
> Is there any way to zero pad the only the values below 10 so I always get two
> character responses for the minutes?
>

This is much simpler with "to_char(timestamp, format)":
http://www.postgresql.org/docs/8.4/static/functions-formatting.html

For example:
select to_char(now(), 'HH:MM');
05:12

select to_char(now(), 'HH24:MM');
17:12

select to_char(now(), 'FMHH:MM');
 5:12

Cheers,
Steve