Обсуждение: Padding time values from EXTRACT()

Hi.

If I use the following snippet

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?

Thanks,

Mark

Mark Kelly <pgsql@wastedtimes.net> writes:
> If I use the following snippet

> EXTRACT (hour FROM timestamp) || ':' ||
> EXTRACT (minute FROM timestamp)
> AS this_time

Use to_char() for this sort of thing.  Something like
to_char(now(), 'HH24:MM') is probably what you want here.

            regards, tom lane

Hi Tom.

On Thursday 19 Nov 2009 at 00:37 Tom Lane wrote:
> Use to_char() for this sort of thing.  Something like
> to_char(now(), 'HH24:MM') is probably what you want here.

Oh whoops. I just realised that was linked from the page I found extract() on.
Guess it's obvious now why I'm on the novice list :)

Thank you for taking the time to reply,

Mark

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