postgresql - Postgres 转换数据类型

Question

我有一列保存为字符数据类型。此专栏是我将用作日期的内容。该列以该格式变为“YYYY-MM-DD”。

这是一个问题，因为如果我需要按日期过滤，我必须去

select col_1, col_2
from table
where date LIKE '2016-04%;

如果我想搜索一个日期范围，这会变成一个巨大的复杂困惑。

将其转换为“日期”数据类型的最简单方法是什么？我希望它继续按 YYYY-MM-DD 顺序排列(无时间戳)。

我的最终目标是能够以如下格式搜索日期:

select col_1, col_2
from table
where date between 2016-01-01 AND 2016-05-31;

你们有什么推荐的？如果我使用 alter 语句转换我的数据类型，我很害怕我会破坏我的日期。 (我保存了一份数据副本，可以再次上传，但需要很长时间。)

编辑:这是一个非常大的表。

编辑第 2 部分:我最初将数据存储为 varchar 数据类型，因为我的日期没有正确上传，当我尝试保存为日期数据类型时收到错误消息。此列中的每个日期均按“YYYY-MM-DD”顺序排列。我的解决方案是将其保存为 varchar 以避免出现错误消息(我不知道哪里出了问题。我什至去掉了前导和尾随空格。)

Answer 1

将日期存储为 varchar 一开始就是错误的选择。很高兴你想改变它。

第一步是使用 ALTER TABLE 语句转换列:

alter table the_table
   ALTER COLUMN col_1 TYPE date using col_1::date, 
   ALTER COLUMN col_2 TYPE date using col_2::date;

请注意，如果您在这些列中有任何值无法转换为正确的日期，这将失败。如果你知道你需要先修复那些无效的字符串，然后才能更改数据类型。

I want it to continue to be in YYYY-MM-DD order

这是一种误解。 DATE(或时间戳)没有“格式”。一旦将其存储为日期，您就可以显示任何您想要的格式。

My ultimate goal is to be able to search for dates in a format like this:

2016-01-01 不是有效的日期文字，可以指定正确的(即正确键入的)日期常量，例如使用 date '2016-01-01'(注意单引号!

因此您的查询变为:

select col_1, col_2
from table
where col_1 between date '2016-01-01' AND date '2016-05-31';

如果您有很多这样的查询，您应该考虑在日期列上创建索引。

---

关于日期常量格式:

Are you telling me that despite having the varchar data types, I can still (as of right now) search between specific dates by just typing the word date and putting single quotes between two dates

不，不是这样的。 SQL 是一种强类型语言，因此只会比较相同类型的值。

使用 ANSI 日期文字(或例如 to_date())会产生类型常量(即具有特定数据类型的值)。

date '2016-01-01' 和 '2016-01-01' 之间的区别与 42(数字)和 '42' 之间的区别相同`(一个字符串)。

如果您将字符串与日期进行比较，您就是在比较苹果和橘子，数据库将执行从一种类型到另一种类型的隐式数据类型转换。这是应该不惜一切代价避免的事情。

如果您不想更改表，您应该使用提供的查询 sagi，它显式地将字符串转换为日期，然后对(实际)日期值(不是字符串)进行比较