Issue with DateTime

[asharpe858]

I've run across a bug when creating an SQL query that's in between two timestamps;

select ToIsoString(@Timestamp),@Id,@Arrived,@Message,@Level,@Properties,@Exception from stream where @Timestamp > DateTime('2022-12-30T20:20:20') and @Timestamp < DateTime('9999-12-30T23:59:59') order by @Arrived limit 20

If I run the above script in SEQ, I get 0 results. However, if I modify the script:

select ToIsoString(@Timestamp),@Id,@Arrived,@Message,@Level,@Properties,@Exception from stream where @Timestamp > DateTime('2022-12-30') and @Timestamp < DateTime('9999-12-30T23:59:59') order by @Arrived limit 20

This will return results

Any idea why adding hours, minutes, seconds to the ISO string in the first DateTime call causes an issue?

[nblumhardt]

Hi! If a time is specified, then the time zone designator must also be present.

E.g. DateTime('2022-12-30T20:20:20Z') will work if you want UTC, or DateTime('2022-12-30T20:20:20+10:00') if you're in GMT + 10h, for example.

I'm not sure why the date/time parser we use is less strict in the date-only case.

Let me know if this helps.

[asharpe858]

That fixed it, thanks!

[nblumhardt]

You're welcome - thanks!