From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Mark Kelly <pgsql(at)wastedtimes(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Padding time values from EXTRACT() |
Date: | 2009-11-19 01:13:05 |
Message-ID: | 4B049BA1.8070902@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Mazen Abdel-Rahman | 2009-11-19 04:14:42 | Using file data as argument to functions called from psql command line |
Previous Message | Mark Kelly | 2009-11-19 00:42:26 | Re: Padding time values from EXTRACT() |