some questions about mimic_derived when i tried to install icustay hourly,sofa and sepsis3.

[njmuyxd]

When i tried to install mimic_derived tables,most of them can be installed correctly,but some of them didn't appear in my navicat like icustay hourly ,sofa, and sepsis3. icustay_hourly.sql error is the grammar error besides 'CAST',how can i fix it?

[alistairewj]

Can you provide more detail on the error and what database system you are using? Which specific tables fail? If it's a Postgres database make sure you use the postgres version of the concepts.

[liuyao123456]

Can you provide more detail on the error and what database system you are using? Which specific tables fail? If it's a Postgres database make sure you use the postgres version of the concepts.

Error: function unnested (integer) does not exist LINE 17: CROSS JOIN UNNEST(all_hours.hrs) AS _t(hr_unnested) ^ HINT: There is no function that matches the specified name and parameter type. You may need to add explicit type conversion.

[liuyao123456]

When i tried to install mimic_derived tables,most of them can be installed correctly,but some of them didn't appear in my navicat like icustay hourly ,sofa, and sepsis3. icustay_hourly.sql error is the grammar error besides 'CAST',how can i fix it?

please run this icustay_hourly.sql: DROP TABLE IF EXISTS icustay_hourly; CREATE TABLE icustay_hourly AS with all_hours as ( select it.stay_id

-- ceiling the intime to the nearest hour by adding 59 minutes then truncating -- note thart we truncate by parsing as string, rather than using DATETIME_TRUNC -- this is done to enable compatibility with psql , PARSE_DATETIME( '%Y-%m-%d %H:00:00', FORMAT_DATETIME( '%Y-%m-%d %H:00:00', DATETIME_ADD(it.intime_hr, INTERVAL '59' MINUTE) )) AS endtime

-- create integers for each charttime in hours from admission -- so 0 is admission time, 1 is one hour after admission, etc, up to ICU disch -- we allow 24 hours before ICU admission (to grab labs before admit) , ARRAY(SELECT * FROM generate_series(-24, CEIL(DATETIME_DIFF(it.outtime_hr, it.intime_hr, 'HOUR')))) as hrs

from mimiciv_derived.icustay_times it ) SELECT stay_id , CAST(hr AS bigint) as hr , DATETIME_ADD(endtime, interval '1' hour * CAST(hr AS bigint)) as endtime FROM all_hours CROSS JOIN UNNEST(all_hours.hrs) AS hr;

[HuilingHU]

when i run the sql from [liuyao123456], it said "function datetime_add(timestamp without time zone, interval) does not exist"

[HuilingHU]

Can you provide more detail on the error and what database system you are using? Which specific tables fail? If it's a Postgres database make sure you use the postgres version of the concepts.

Error: function unnested (integer) does not exist LINE 17: CROSS JOIN UNNEST(all_hours.hrs) AS _t(hr_unnested) ^ HINT: There is no function that matches the specified name and parameter type. You may need to add explicit type conversion.

you can try: the last line of the icustay_hourly.sql----> CROSS JOIN UNNEST(array[all_hours.hrs]) AS _t(hr_unnested)

[liuyao123456]

when i run the sql from [liuyao123456], it said "function datetime_add(timestamp without time zone, interval) does not exist"

please run postgres-functions.sql and make sure correctly dateadd_add functions.

[damaohongtu]

-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS mimiciv_derived.icustay_hourly; CREATE TABLE mimiciv_derived.icustay_hourly AS
/* This query generates a row for every hour the patient is in the ICU. */ /* The hours are based on clock-hours (i.e. 02:00, 03:00). */ /* The hour clock starts 24 hours before the first heart rate measurement. */ /* Note that the time of the first heart rate measurement is ceilinged to */ /* the hour. */ /* this query extracts the cohort and every possible hour they were in the ICU */ /* this table can be to other tables on stay_id and (ENDTIME - 1 hour,ENDTIME] */ /* get first/last measurement time */
WITH all_hours AS (
  SELECT
    it.stay_id, /* round the intime up to the nearest hour */
    CASE
      WHEN DATE_TRUNC('HOUR', it.intime_hr) = it.intime_hr
      THEN it.intime_hr
      ELSE DATE_TRUNC('HOUR', it.intime_hr) + INTERVAL '1 HOUR'
    END AS endtime, /* create integers for each charttime in hours from admission */ /* so 0 is admission time, 1 is one hour after admission, etc, */ /* up to ICU disch */ /*  we allow 24 hours before ICU admission (to grab labs before admit) */
    GENERATE_SERIES(-24, CAST(CEIL(EXTRACT(EPOCH FROM it.outtime_hr - it.intime_hr) / 3600.0) AS INT)) AS hrs /* noqa: L016 */
  FROM mimiciv_derived.icustay_times AS it
)
SELECT
  stay_id,
  CAST(hr_unnested AS BIGINT) AS hr,
  endtime + CAST(hr_unnested AS BIGINT) * INTERVAL '1 HOUR' AS endtime
FROM all_hours
CROSS JOIN UNNEST(ARRAY[all_hours.hrs]) AS _t(hr_unnested)