时间戳

首页 > 解决方案 > 在sql中将日期列从文本数据类型转换为日期类型

sql - 在sql中将日期列从文本数据类型转换为日期类型

问题描述

我在 postgres 中有一张名为my_table.

在此表中，我有一个名为date_text文本类型的列，其中包含以下格式的日期：01-31-2020. 我想直接将此列从文本类型转换为日期类型。使用下面的代码，我可以获得日期类型的列。但是，它的格式错误：2020-01-31. 如何以正确的格式 ( 01-31-2020) 获取它以及如何直接更新该列？我是 sql 新手。

SELECT TO_Date(date_text,'DD-MM-YYYY') as date_date
FROM my_table;

标签： sqlpostgresqldatedatetime

解决方案

日期没有格式，它们是二进制类型。格式仅在字符串被解析为日期或日期被格式化为字符串时适用。

您看到的是您的客户端应用程序如何显示日期。甚至数据库管理工具也只是客户端。ISO8601 格式 (YYYY-MM-DD) 是标准日期格式，明确且可排序，这就是数据库工具使用它来显示日期的原因。

另一方面，04-07-2021是不可排序和模棱两可的。是 4 月 7 日还是 7 月 4 日？以这种方式犯错误太容易了，导致业务问题、失去最后期限、航班、金钱或更糟。

想象一下成为警方调查的目标，因为人们使用了模糊的本地化日期字符串。

您问题的真正解决方案是将文本列转换为。date列并让客户按照它认为合适的方式显示它。

即使您继续使用TO_Date(date_text,'DD-MM-YYYY')，您也必须配置客户端以格式化日期。

date可以使用将列的类型更改为ALTER TABLE ... SET DATA TYPE。借用这个问题的答案：

ALTER TABLE books 
ALTER COLUMN date_text SET DATA TYPE date
      USING to_date(date_text, 'mm-dd-yyyy');

我假设datet_text不是真实姓名，否则该列也可能需要重命名。这反过来可能会破坏查询和视图。

这就是为什么在列名中指定类型是一个坏主意，无论是作为前缀还是后缀

推荐阅读