类型时间戳记的无效输入语法 [英] invalid input syntax for type timestamp

问题描述

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

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

推荐答案

请参见

返回值的类型为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.

这篇关于类型时间戳记的无效输入语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！