类型时间戳记的无效输入语法

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

在运行下面的代码时，我收到一条错误消息，说明从入院日期时间开始的时间戳类型的输入语法无效.

While running the below code i get an error saying invalid input syntax for type timestamp from admission_datetime.

UPDATE ccsm.stg_demographics_baseline SET xx_los_days = (CASE WHEN admission_datetime IS NULL OR date_trunc('day',admission_datetime) = '' THEN NULL WHEN discharge_datetime IS NULL OR date_trunc('day',discharge_datetime) = '' THEN date_diff('day', admission_datetime, CURRENT_DATE) ELSE date_diff('day', admission_datetime, discharge_datetime) END); enter code here

推荐答案

请参见 date_trunc文档:

返回值的类型为timestamp或interval，所有不如所选字段重要的字段都设置为零(对于日和月，则设置为零).

The return value is of type timestamp or interval with all fields that are less significant than the selected one set to zero (or one, for day and month).

因此您不能将其与空字符串进行比较:

So you can not compare it with an empty string:

date_trunc('day', admission_datetime) = ''

invalid input syntax for type timestamp错误消息涉及空字符串('')，而不是admission_datetime列.

The invalid input syntax for type timestamp error message concerns the empty string (''), not the admission_datetime column.

此外，PostgreSQL中没有date_diff函数.只需从另一个减去一个timestamp，您将得到一个interval结果:

Furthermore, there is no date_diff function in PostgreSQL. Just subtract one timestamp from another and you will get an interval result:

SELECT timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'

你会得到

interval '1 day 15:00:00'

如果您需要天天的时间差，请尝试以下操作:

If you need the difference in days, try this:

SELECT DATE_PART('day', timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00')

结果是1.

有关PostgreSQL中类似DATEDIFF的表达式的示例，请参见此处.

See here for examples of DATEDIFF-like expressions in PostgreSQL.