sql - sql语言 - 使用AWS athena的时间戳差异

我在AWS Athena中Jupyter Notebook运行SQL查询,如下所示,计算时间戳之间的差异,如下所示。


query_demog ="""



select ad.subject_id, ad.hadm_id, i.icustay_id ,


 date_diff('second', timestamp '1970-01-01 00:00:00', ad.admittime) as admittime,


 date_diff('second', timestamp '1970-01-01 00:00:00', ad.dischtime) as dischtime,


 ROW_NUMBER() over (partition by ad.subject_id order by i.intime asc) as adm_order,


 case when i.first_careunit='NICU' then 5


 when i.first_careunit='SICU' then 2


 when i.first_careunit='CSRU' then 4


 when i.first_careunit='CCU' then 6


 when i.first_careunit='MICU' then 1


 when i.first_careunit='TSICU' then 3


 end as unit,


 date_diff('second', timestamp '1970-01-01 00:00:00', i.intime) as intime,


 date_diff('second', timestamp '1970-01-01 00:00:00', i.outtime) as outtime,


 i.los,


from mimiciii.admissions ad,


 mimiciii.icustays i,


 mimiciii.patients p


where ad.hadm_id=i.hadm_id and p.subject_id=i.subject_id 


order by subject_id asc, intime asc



"""



它可以正常工作,现在,当我包含另一行时,得到一个错误,


query_demog ="""



select ad.subject_id, ad.hadm_id, i.icustay_id ,date_diff('second', timestamp '1970-01-01 00:00:00', ad.admittime) as admittime, date_diff('second', timestamp '1970-01-01 00:00:00', ad.dischtime) as dischtime, ROW_NUMBER() over (partition by ad.subject_id order by i.intime asc) as adm_order, case when i.first_careunit='NICU' then 5 when i.first_careunit='SICU' then 2 when i.first_careunit='CSRU' then 4 when i.first_careunit='CCU' then 6 when i.first_careunit='MICU' then 1 when i.first_careunit='TSICU' then 3 end as unit, date_diff('second', timestamp '1970-01-01 00:00:00', i.intime) as intime, date_diff('second', timestamp '1970-01-01 00:00:00', i.outtime) as outtime, i.los,



 EXTRACT(EPOCH FROM (i.intime-p.dob)::INTERVAL)/86400 as age



from mimiciii.admissions ad, mimiciii.icustays i, mimiciii.patients p



where ad.hadm_id=i.hadm_id and p.subject_id=i.subject_id 



order by subject_id asc, intime asc



"""



EXTRACT(EPOCH FROM (i.intime-p.dob)::INTERVAL)/86400 as age

调用(InvalidRequestException )StartQueryExecution操作时出现错误:

我不知道为什么包含EXTRACT(EPOCH FROM (i.intime-p.dob)::INTERVAL)/86400 as age会出现错误

时间: 原作者:

to_unixtime() 应该可以工作:


to_unixtime(i.intime-p.dob)/86400 as age



原作者:
...