Postgresql：日期格式和本地语言输出 [英] Postgresql: date format and local language output

问题描述

我对postgresql有两个问题。
第一个是我必须将日期转换为特定格式，例如：2016年11月4日星期五

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 ...

这是结果： https://i.stack.imgur.com/RjNnm.png

乍一看，tstamp表消失了，字符串中还有更多空间：

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

friday....04.November..2016

*十六进制编码

第二个是我必须将其转换为语言格式，例如意大利语：Venerdì2016 Novembre 2016。

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

感谢帮助

推荐答案

关于第一个问题：额外的空格是因为 month 和日模式为：

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

空白填充为9个字符

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')

-

关于意大利语，也许还有另一种方法，但这也应该起作用。您可以对意大利的月份和日期名称进行硬编码，并使用 case 表达式翻译它们，如下所示：

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

的tstamp

请注意，如果要随时正确使用此名称，则应将所有7天和12个月的名称都放在大小写表达式中。

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

甚至更好的是， case 语句，可以使用 D 模式确定日期和 MM 月的模式。如果您想查看可用模式列表，则可以点击上面我已发布的链接。

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.

这篇关于Postgresql：日期格式和本地语言输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！