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: (view raw, whole thread or download thread mbox)
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)":

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

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

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


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-2017 The PostgreSQL Global Development Group