How to get postgres sql timestamp in 'DD-Mon-YYYY' format?

Question

My Datestyle:

=> show datestyle;
    DateStyle
   -----------
     ISO, YMD

Its showing format as:

=> select now()::DATE;
now
------------
 2013-10-17

But I want the output:

now
------------
 17-Oct-2013

This can be done using TO_CHAR(timestamp,'MON-YY') but its datatype changes to character string I want the datatype as timestamp only

Answer 1

Formatting of values should happen in the application or client and not in the database layer.

Answer 2

I don't think you will be able to preserve the format without changing the date style. Or as @Michael-O has suggested, you can fetch and format at the business level

Answer 3

My application is purely big data solution for large set of data so I want maximum utilization of database functionality than writing customize code at application level.

This can be done by using one hack: As I said TO_CHAR(timestamp,'MON-YY') can be used to convert type.

We can simply convert TO_CHAR(timestamp,'MON-YY') value to date by using ('01-' || TO_CHAR(timestamp,'MON-YY')):: DATE