DATETIME_DIFF implementation varies between postgres and bigquery

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:

mimic-code/mimic-iv/concepts/measurement/urine_output_rate.sql

Lines 44 to 53 in a830870

    
           -- 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.

PR up: urine_output_rate uses SECOND-based diffs like kdigo_uo.

Opened #2059 — FLOOR on postgres DATETIME_DIFF (iii/iv) to match BQ truncation.

addressed in #2124

urine_output_rate now uses SECOND-based DATETIME_DIFF like kdigo_uo. See #2124.

urine_output_rate now uses SECOND-based DATETIME_DIFF in #2124.

Fixed in #2126 — urine_output_rate now uses second-based DATETIME_DIFF like kdigo_uo.

Navigation Menu

Repository navigation

`DATETIME_DIFF` implementation varies between postgres and bigquery #1549

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	-- 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

DATETIME_DIFF implementation varies between postgres and bigquery #1549

Description

Activity

chessing234 commented on Jul 16, 2026

chessing234 commented on Jul 16, 2026

chessing234 commented on Jul 23, 2026

chessing234 commented on Jul 23, 2026

chessing234 commented on Jul 23, 2026

chessing234 commented on Jul 25, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

`DATETIME_DIFF` implementation varies between postgres and bigquery #1549