Skip site navigation (1) Skip section navigation (2)

Re: Padding time values from EXTRACT()

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 (view raw or flat)
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


In response to

pgsql-novice by date

Next:From: Mazen Abdel-RahmanDate: 2009-11-19 04:14:42
Subject: Using file data as argument to functions called from psql command line
Previous:From: Mark KellyDate: 2009-11-19 00:42:26
Subject: Re: Padding time values from EXTRACT()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group