Postgresql: date format and local language output

Question

I have two problem with postgresql. The first one is I have to convert date in specific format like: friday 04 november 2016

SELECT to_char(tstamp, 'Day DD month YYYY') FROM ...

that's the result: https://i.stack.imgur.com/RjNnm.png

at first look the tstamp table disappeared and there're more space in the string:

friday....04.November..2016

*Hex encoding

the second one is that i have to convert it in a language format, in italian for example: "Venerdì 04 Novembre 2016".

Thank's for help

Answer 1

About first question: additional spaces are because month and Day patterns are:

blank-padded to 9 chars

https://www.postgresql.org/docs/9.6/static/functions-formatting.html

So, if you want to remove this spaces, you can try something like this:

 select trim(to_char(localtimestamp(0), 'Day'))||to_char(localtimestamp(0), ' DD ')||trim(to_char(localtimestamp(0), 'month'))||to_char(localtimestamp(0), ' YYYY')

--

About italian language, may be there is another ways too, but this should also work. You can hard coded italian month and day names and "translate" they using case expression, something like this:

select 
    case 
        when trim(to_char(tstamp, 'Day')) = 'Monday' then 'Monday_in_italian' 
        when trim(to_char(tstamp, 'Day')) = 'Tuesday' then 'Tuesday_in_italian'
        when trim(to_char(tstamp, 'Day')) = 'Wednesday' then 'Wednesday_in_italian' 
        -- another days here
    end||
    to_char(tstamp, ' DD ')||
    case 
        when trim(to_char(tstamp, 'month')) = 'january' then 'January_in_italian'
        when trim(to_char(tstamp, 'month')) = 'february' then 'February_in_italian'
        -- another months here
    end||
    to_char(tstamp, ' YYYY')
    as tstamp 
    from your_table

Note, that you should put all 7 day and all 12 month names in case expressions, if you want work this correctly for anytime.

Or even better, in case statements, you can use D pattern for determine day and MM pattern for month. If you want see available patterns list, you can follow link, I've posted above.

Answer 2

Oto's answer is all good, but with excessive coding. If your OS does not support Italian localization (locale -a), go with it. But if it does, you can format your time like here:

t=# set lc_time to 'it_IT.utf8';
SET
Time: 0.295 ms
t=# select to_char(now(), 'TMDay, DD TMMonth YYYY');
         to_char
-------------------------
 Giovedì, 11 Maggio 2017
(1 row)

Time: 3.705 ms

Formats anf TM prefix explanation can be found in docs