在运行下面的代码时,我收到一条错误消息,说明从入院日期时间开始的时间戳类型的输入语法无效.
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.
更多推荐
类型时间戳记的无效输入语法
发布评论