将Varchar时间戳记为PostgreSQL

本文介绍了将Varchar时间戳记为PostgreSQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想做的是将日期列类型从varchar更改为不带时区的时间戳，并迁移所有数据.这是我在做什么:

What i want to do, is to change my date column type from varchar to timestamp w/o timezone, and migrate all my data. This is what im doing:

ALTER TABLE mytable ALTER COLUMN "datecol" TYPE timestamp without time zone USING(to_timestamp("datecol", 'YYYY-MM-DD')::timestamp without time zone);

如果datecol中的数据为日期格式，则此方法很好用.但是，如果我有一些无效数据，例如随机字符串(例如"abc")，如何验证并检查日期格式是否合适?我想为那些无效的字段设置默认值.

This works fine if data in datecol is in date format. But if i have some not valid data, like random strings (e.g "abc") how can i validate and check if date format is good? I want to set default value for those invalid fields.

感谢Ludvig，我解决了我的问题:

Thanks Ludvig, i solved my problem:

create or replace function is_date(s varchar) returns boolean as $$ begin perform s::date; return true; exception when others then return false; end; $$ language plpgsql; ALTER TABLE mytable ALTER COLUMN "datecol" TYPE timestamp without time zone USING( CASE WHEN is_date("datecol") = true THEN to_timestamp("datecol", 'YYYY-MM-DD')::timestamp without time zone ELSE '1970-01-01 00:00:00.000' END );

推荐答案

我将创建一个函数，尝试使用不同的格式进行强制转换，并在没有一个有效日期的情况下返回默认日期，而不是测试有效日期:

Instead of testing for a valid date I would create a function that tries casting using different formats and returns the default date in case none of them work:

create or replace function convert_date(s varchar) returns timestamp as $$ begin -- test standard ISO format begin return to_timestamp(s, 'yyyy-mm-dd'); exception when others then -- ignore end; begin return to_timestamp(s, 'yyyy.mm.dd'); exception when others then -- ignore end; begin return to_timestamp(s, 'yyyy/mm/dd'); exception when others then -- ignore end; begin return to_timestamp(s, 'yyyy-mm'); exception when others then -- ignore end; begin return to_timestamp(s, 'yyyy'); exception when others then -- ignore end; return timestamp '1970-01-01 00:00:00'; end $$ language plpgsql;

然后使用:

ALTER TABLE mytable ALTER COLUMN "datecol" TYPE timestamp without time zone USING (convert_date(datecol));

这不是很有效，但是对于一次性工作，它应该可以工作

This won't be very efficient, but for a one-time job it should work