I'm trying to understand how the date and datetime should work in the Ignition Report module, particualrly relating to parameters. If I define a parameter "Today_Date", I can specify the parameter type as "Date" - fine, and the default value I can define as now(). That all makes sense - except when I look in the preview, the data shows this as actually being a datetime, which is a different data type.
I have an issue where a field in a database I need to compare to is actually type Date - not Datetime. Even if I look in the available "Date and Time" functions in the expression function list (the sum symbol on the right side of the "Default Value (expression)" text entry window in the Data tab) it lists all the relevant functions there as returning "Date" type data - but is this actually universally DateTime type?
Does this mean that as a general rule I'd actually need to convert any Date type fields in my SQL to Datetime as part the query before I can usefully do comparisons?
Put up a question and then I come up with a different solution that I should have thought of . . . using the midnight function.
More generally, I'm still interested in best practices with using dates in the reporting module, if anyone has thoughts on that.
I am not sure that there is a datetime data type in the Ignition Report module.
It looks like the Date datatype is a java.util.Date, which includes both date and time.
Stripping the time off of a datetime or unixdate or java.util.Date for comparisons is a common requirement. Solutions are generally inelegant and database dependent.
The midnight function is probably cleaner than most. You will want to answer the question of "midnight where"? Timezones can be an issue in unexpected places.
Java's Date type, contrary to the name, represents a timezone-less instant-in-time. So with a known timezone you can 'normalize' a date to a particular date-time-in-that-timezone; generally speaking Ignition does this for you automatically when it represents a Date as a string for display, using the timezone of the local Java process.
1 Like
In my case, I think that I can ignore timezone complications. Any relevance to a timezone in this particular data will always be to our local timezone.