Setup of report parameters for filtering by date might be a bit tricky, especially in case of timestamp fields because they cannot be filtered with simple equality comparison with some date. Let's discover how to do setup report parameters for typical usage scenarious.
This is simplest case when data range is specified with 2 separate report parameters:
Application of these parameters in the data source query is rather trivial:
SELECT * FROM some_table WHERE 1=1 @start_date[ and timestamp_column >= {0} ] @end_date[ and timestamp_column < {0} ]
{ $and: [ {"_id":{$exists:true}}, @start_date[ {{ "timestamp_field" : {{ $gte: {0} }} }}, ] @end_date[ {{ "timestamp_field" : {{ $lt: {0} }} }}, ] ] }
"1"="1" @start_date[ and "timestamp_fld":field>="{0}":var ] @end_date[ and "timestamp_fld":field<"{0}":var ]
You may notice that start_date is inclusive (meaning is "starting from this date"),
and end_date is exclusive (meaning is "everything before this date").
If you want to make end_date inclusive you can specify the following Expression in the parameter configuration:
Parameter["end_date"]!=null ? Parameter["end_date"].AddDays(1) : null
.
In some cases you might want to filter data by exact date. For database 'Date' column (field) application of the date filter parameter is trivial, however in case of timestamps you'll need to introduce additional (hidden) parameter that calculates 'next day date':
Expression for this parameter should be: Parameter["exact_date"]!=null ? Parameter["exact_date"].AddDays(1) : null
.
Application of these parameters in the data source query is exactly the same as in case of start/end parameters (described above) where
exact_date corresponds to start date, and exact_date_next_day is an end date respectively.
Another typical case for operational reports is filtering by custom pre-defined date ranges. For this purpose you need to configure the following report parameters:
Parameter["date_range"]!=null ? Date.Now().Date.AddDays(-Parameter["date_range"]) : null
Date.Now()
returns server's date/time. To make the calculation independent from server's timezone offset you may use
Date.UtcNow()
and with .AddHours(offsetInHours)
get timestamp exactly in your timezone.
SeekTable doesn't handle timezones automatically on its side; this means that unmodified Date/Time value is used in the data query condition. From this perspective, the following situations are possible:
Date.UtcNow()
result with AddHours
method.
Parameter["start_date"]!=null ? Parameter["start_date"].AddHours(offsetInHours) : null
.
Many timezones have daylight saving time shift, and if you don't want to change the constant in expressions twice per year you can use
Date.TimezoneUtcOffsetHours("linux_timezone_name")
function (see list of TZ names).
For example, Date.TimezoneUtcOffsetHours("CET")
returns 2
in daylight saving period and 1
otherwise.