How can we search the json file based on date field using S3 select

Question

I am trying to query data on JSON file using S3-Select. I am unable to filter based on the date field. I have tried using current_date, sysdate and a few CAST options too. I am planning to compute the curent_date and send it from API. Before that, I wanted to check if there was any other way to get the current date in the query.

Sample Json :

S3 Select Queries I have tried :

select * from S3Object[*].stations[*] as station where station.iataCd = 'NCL' and station.expiryDt >= sysdate();
select * from S3Object[*].stations[*] as station where station.iataCd = 'NCL' and station.expiryDt >= current_date;
select * from S3Object[*].stations[*] as station where station.iataCd = 'NCL' and station.expiryDt >= '2023-01-23'

Answer 1

you can get the current UTC timestamp with UTCNOW() date function, if that is what you want

UTCNOW() gives you current UTC time as timestamp: 2017-10-13T16:02:11.123Z