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

Oto Shavadze · Accepted Answer · 2017-05-10T15:19:39.297

5

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.

edited May 10 '17 at 15:19

answered May 10 '17 at 14:15

Oto Shavadze

40,603
55
152
236

ok thanks but i don't need localtimestamp cause tstamp is a name of a table that contain the timestamp: http://imgur.com/a/PnvYY . that's is original query SELECT tstamp from table and select trim(to_char(tstamp, 'Day')) doesn't work: http://imgur.com/a/eS48T – Alessandro95 May 10 '17 at 14:23
What is `tstamp` column type? – Oto Shavadze May 10 '17 at 14:28
tstamp is the name of column, I've tried your code and that's the result: http://imgur.com/a/8M2nj – Alessandro95 May 10 '17 at 14:29
timestamp(6) NOT NULL – Alessandro95 May 10 '17 at 14:30
Well, is this what you want? (result on image) – Oto Shavadze May 10 '17 at 14:30
yes that's it but why "?column" and not "tstamp" like the original name? – Alessandro95 May 10 '17 at 14:32
add at the end `as tstamp` – Oto Shavadze May 10 '17 at 14:34
like this: `select trim(to_char(tstamp, 'Day'))||to_char(tstamp, ' DD ')||trim(to_char(tstamp, 'month'))||to_char(tstamp, ' YYYY') as tstamp from your_table` – Oto Shavadze May 10 '17 at 14:36
it works thanks in part cause i wish to see the output in italian and it end – Alessandro95 May 10 '17 at 14:38
yes, is there a way to convert the output in italian? Something like: Venerdì 04 Novembre 2016? – Alessandro95 May 10 '17 at 14:48
Yes, there is one way, just a moment – Oto Shavadze May 10 '17 at 14:48
@Alessandro95 - added one solution for italian language – Oto Shavadze May 10 '17 at 15:09

score 5 · Answer 2 · answered May 11 '17 at 08:28

5

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

answered May 11 '17 at 08:28

Vao Tsun

47,234
13
100
132

1

is there a way to use this in a SELECT, instead of psql? – João Ciocca Feb 15 '18 at 19:22
sure. the sql above is not psql specific – Vao Tsun Feb 15 '18 at 20:46
Exellent! `TMMonth` and `TMDay` for date format return indeed trimmed full month/day name. Thanks a mil @VaoTsun! – Camille Jul 07 '23 at 12:28

Postgresql: date format and local language output

2 Answers2

Linked