CODEWITHSUNDEEP
postgresqlpostgresql-9.2
user3946530
user3946530

Reputation:

How to use date format in postgresql?

I am using the following query:

SELECT distinct to_char(order_date, 'Mon') as mon from meta.ship_error order 
by to_char(order_date, 'Mon') asc

and the output is:

 "Apr" 
 "Aug" 
 "Dec" 
 "Feb" 
 "Jan" 
 "Jun" 
 "Mar" 
 "May" 
 "Nov" 
 "Oct" 
 "Sep" 
  ""

but I want output like:

 jan
 feb
 mar
 apr  ....

What changes should I do In my query?

Upvotes: 1

Views: 70

Answers (2)

IMSoP
IMSoP

Reputation: 97996

You are ordering by the textual representation of the month, here:

order by to_char(order_date, 'Mon') asc

The result of to_char is a string, so the ordering is alphabetical; Postgres no longer knows this has anything to do with dates, so has no reason to put "jan" before "apr".

You want to order by either the date itself:

order by order_date asc

Or by a numeric representation of the month, which you could get using extract():

order by extract(month from order_date) asc

Note that since your DISTINCT is currently grouping based on the textual representation, you may have trouble making the SELECT and ORDER BY use different representations. This will be easier with an explicit GROUP BY, which can list both representations:

group by extract(month from order_date), to_char(order_date, 'Mon')

Since you can be sure that a given order_date can't produce a separate value for those two columns, this won't change the number of rows, but allows you to write this:

select to_char(order_date, 'Mon')
from meta.ship_error
group by extract(month from order_date), to_char(order_date, 'Mon')
order by extract(month from order_date);

Upvotes: 3

user3946530
user3946530

Reputation:

using this query i got my solution

SELECT distinct to_char(order_date, 'Mon') as MON , extract(month from 

order_date) from meta.ship_error order by extract(month from order_date) asc

Upvotes: 0

Related Questions