Skip to content

DATETIME_DIFF implementation varies between postgres and bigquery #1549

@alistairewj

Description

@alistairewj

As raised in #1529, DATETIME_DIFF in PostgreSQL calculates a fractional number, whereas DATETIME_DIFF in BigQuery returns a whole number. This results in a subtle divergence of the concepts in the two dialects. urine_output_rate may be affected as various comparisons expected fractional values to be returned:

-- note that we assume data charted at charttime corresponds
-- to 1 hour of UO, therefore we use '5' and '11' to restrict the
-- period, rather than 6/12 this assumption may overestimate UO rate
-- when documentation is done less than hourly
, SUM(CASE WHEN DATETIME_DIFF(io.charttime, iosum.charttime, HOUR) <= 5
THEN iosum.urineoutput
ELSE null END) AS urineoutput_6hr
, SUM(CASE WHEN DATETIME_DIFF(io.charttime, iosum.charttime, HOUR) <= 5
THEN iosum.tm_since_last_uo
ELSE null END) / 60.0 AS uo_tm_6hr

Easiest fix is to (1) update postgres-functions.sql and (2) update the UO query to take a similar approach to the kdigo_uo query.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions