PostgreSQL formatting time aggregate function date('date_format')

Question

Is there any aggregate function to format date & time (however I am interested in interval more) in a custom way like 11:10:45 to 11h 10m 45s or something like that in PostgreSQL ? or I need to extract the parts or concat them by myself ?

Problem occurs specially with number of days in interval

Why do you need aggregate function? Could you post your SQL here? — vyegorov
– vyegorov, Commented Apr 30, 2012 at 19:47
I assume that "aggregate" in the question is just a misunderstanding and the OP is looking for a plain function. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 1, 2012 at 15:31
actually I thought there already is some functin like AVG() or SUM() for this purpose. So I kept an aggregate there. should I remove that from title ? — Neel Basu
– Neel Basu, Commented May 1, 2012 at 17:26
what would the aggregate function have to do? Compute the interval between the first an the last timestamp in the set? Maybe show an example. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 5, 2012 at 16:44

Marc Morin · Accepted Answer · 2012-04-30 19:46:24Z

1

Check out the full documentation for the to_char function, it can do what you want

http://www.postgresql.org/docs/9.1/interactive/functions-formatting.html

answered Apr 30, 2012 at 19:46

Marc Morin

4042 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Neel Basu Over a year ago

Yes but would they work same in interval too ? What If I want to get number of days in interval. where as DDD returns day of year (001-366)

Marc Morin Over a year ago

I don't think there is a built-in number of days formatter. You'll need to build it up from the seconds using (extract(epoch from interval '3days') / (24*3600))::int or something similar

vyegorov · Accepted Answer · 2012-05-01 14:39:33Z

Your question is not quite clear. If you want to retrieve the

number of days in interval

You can use EXTRACT:

SELECT EXTRACT(day from '7 week'::interval) AS d

 d
----
 49

Note that this extracts a subfield of an interval as given. the interval is not normalized or justified. To justify the interval first use justify_interval()

SELECT EXTRACT(day from justify_interval('7 week'::interval)) AS d;

 d
----
 19

A month of 30 days has been deducted from the 49 days.

Accordingly these expressions return 0 days:

SELECT EXTRACT(day from '24h'::interval);
SELECT EXTRACT(day from '1 months'::interval);

If you just want the number of days that lie between dates:

SELECT '2012-3-23'::date - '2012-3-1'::date AS d;

 d
----
 22

You can just subtract dates to get an integer signifying the number of days in between.

Or between timestamps:

SELECT EXTRACT (day from '2012-03-25 23:12'::timestamp
                       - '2010-03-01 03:34'::timestamp) AS d;

  d
-----
 755

Matthew Wood · Accepted Answer · 2012-04-30 20:19:04Z

0

Try setting different values of the PostgreSQL setting intervalstyle:

db=# show intervalstyle ;
 IntervalStyle 
---------------
 postgres

db=# select interval '3 days 11:10:45';
    interval     
-----------------
 3 days 11:10:45

db=# set intervalstyle = 'postgres_verbose';
SET

db=# select interval '3 days 11:10:45';
             interval              
-----------------------------------
 @ 3 days 11 hours 10 mins 45 secs

db=# set intervalstyle = 'iso_8601';
SET

db=# select interval '3 days 11:10:45';
   interval    
---------------
 P3DT11H10M45S

answered Apr 30, 2012 at 20:19

Matthew Wood

16.5k6 gold badges49 silver badges35 bronze badges

Collectives™ on Stack Overflow

PostgreSQL formatting time aggregate function date('date_format')

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related