PostgreSQL：将字符串转换为日期 DD/MM/YYYY

Question

我正在尝试将一个CHARACTER VARYING列转换为DATE，但我需要像这样的日期格式：DD/MM/YYYY。我使用以下SQL查询：

ALTER TABLE test 
ALTER COLUMN date TYPE DATE using to_date(date, 'DD/MM/YYYY');

结果是这样的日期格式：YYYY-MM-DD。

我该如何获得格式为DD/MM/YYYY的日期？

非常感谢！

Thomas

- wiltomap

Answer 1

DATE列没有格式，您无法为其指定格式。

您可以使用DateStyle来控制PostgreSQL发出日期的方式，但它是全局的且有些受限。

相反，您应该在查询时使用to_char对日期进行格式化，或者在客户端应用程序中进行格式化。例如：

SELECT to_char("date", 'DD/MM/YYYY') FROM mytable;

例如

regress=> SELECT to_char(DATE '2014-04-01', 'DD/MM/YYYY');
  to_char   
------------
 01/04/2014
(1 row)

- Craig Ringer

---

好的，非常感谢！我想确保这不可能通过to_date()函数实现。 - wiltomap

@wiltomap 不是这样的，因为 to_date 只是从字符串中创建一个日期。日期在内部没有格式，它只是一个数字。 - Craig Ringer

有没有办法以这种方式搜索数据库？例如：select to_char(DATE my_column, 'DD/MM/YYYY') from my_table order by (DATE my_column) desc？ - antjanus

我有什么遗漏吗？我收到了错误信息：函数to_char(character varying, unknown)不存在。 - Luffydude

我正在使用postgresql，我在别处找到了一些代码，它起作用了，但还是谢谢。 - Luffydude

Answer 2

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

SELECT to_char(date_field, 'DD/MM/YYYY')
FROM table

- Fellipe Sanches

Answer 3

文档中提到：

日期/时间类型的输出格式可以设置为四种样式之一：ISO 8601、SQL（Ingres）、传统的POSTGRES（Unix日期格式）或德语。默认值是ISO格式。

因此，可以使用 postgres 控制日期时间输出格式，例如：

t=# select now();
              now
-------------------------------
 2017-11-29 09:15:25.348342+00
(1 row)

t=# set datestyle to DMY, SQL;
SET
t=# select now();
              now
-------------------------------
 29/11/2017 09:15:31.28477 UTC
(1 row)

t=# select now()::date;
    now
------------
 29/11/2017
(1 row)

注意，如@Craig在他的答案中提到的那样，改变datestyle也会（首先）改变Postgres解析日期的方式。

- Vao Tsun

---

我尝试了 select now()::date，在一个服务器上返回日期，在另一个服务器上返回时间戳。它们都运行着 postgres 11.12。我怀疑有人可能在返回时间戳的服务器上搞乱了日期转换或类型函数，但我不知道是如何或在哪里进行的。任何帮助调查的支持都将不胜感激。 - Brian D

啊，原来是因为一个服务器的edb_redwood_date设置为“on”，而另一个服务器的设置为“off”，请参见：https://www.enterprisedb.com/docs/epas/latest/epas_compat_ora_dev_guide/01_introduction/01_edb_redwood_date/ 解决方案是alter system set edb_redwood_date=off; select pg_reload_conf();。 - Brian D

Answer 4

如果您需要将select语句返回的日期转换为特定格式，则可以使用以下方法：

select to_char(DATE (*date_you_want_to_select*)::date, 'DD/MM/YYYY') as "Formated Date"

- Giorgos Tsakonas

Answer 5

根据您需要的输出类型而定，但以下是基于时间间隔的两个快速示例：

• SELECT (now() - interval '15 DAY')::date AS order_date -> 2021-07-29
• SELECT to_char(now() - interval '15 DAY', 'YYYY-MM-DD') -> 2021-07-29

- d_-

Answer 6

假设你的日期列是order_date:

SELECT (
    RIGHT(order_date, 4) 
    || '-' 
    || SUBSTRING(order_date, 4, 2) 
    || '-' 
    || LEFT(order_date, 2)
    )::DATE
FROM test

或者

SELECT CAST(
    RIGHT(order_date, 4) 
    || '-' 
    || SUBSTRING(order_date, 4, 2) 
    || '-' 
    || LEFT(order_date, 2)
    AS DATE )
FROM test

- AK91