CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Neel Basu
Neel Basu

Reputation: 12904

PostgreSQL formatting time aggregate function date('date_format')

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

Upvotes: 0

Views: 1669

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

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

Upvotes: 1

Matthew Wood
Matthew Wood

Reputation: 16417

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

Upvotes: 0

Marc Morin
Marc Morin

Reputation: 404

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

Upvotes: 1

Related Questions