Date column filter with lte, equal or gte

[jplevene]

I want to add a filter for a date column with lte, equal or gte options for the date selected in the datepicker.

• The filter is only for the date (not the time) and each cell is formatted "YYYY-MM-DD hh:mm:ss" and is UTC.
• Each cell has a render that converts the UTC to local so it is displayed in the local format and timezone in the cell (data is still UTC)
• The filter is to work only on the date part, being lte, equal OR gte (ignore the time part)
• The date picker is obviously local format and timezone
• I also use moment (see examples below)

Basically I need to have a datepicker in the column filter, limited to a choice of lte, equal or gte and the date chosen in the drop down fileter needs to be converted to UTC before compare to the data (I could even include the time at start and end of the day for the compare so it could be a simple string compare)


I use moment so I can convert a local date to UTC by:
as_utc = moment(local_date).utc();
startUtc = moment(local_date).startOf('day').utc().format("YYYY-MM-DD HH:mm:ss");
endUtc = moment(local_date).endOf('day').utc().format("YYYY-MM-DD HH:mm:ss");
etc...

[paramvir]

To handle date filtering while maintaining UTC data, a more streamlined approach is to use a

Formula Column with a getter/setter rather than overriding multiple individual properties.

Recommended Approach: Two-Way Column Dependency

Instead of applying manual customizations to column.render and column.filter.conditions for every logic check, you can set up a dependency between a visible "Local" column and a hidden "UTC" column.

• How it works: The visible column uses a get function to translate the hidden UTC data into a local format for both display and filtering. If editing is required, a set function translates the value back to UTC.
  
• Result: This allows the grid's native filtering to work on local date strings without complex custom logic.
  

Key Resources

• Date Filtering: This example demonstrates how to integrate the date/time picker into the filter:
  https://paramquery.com/pro/demos/filter_date
  
  
• Condition Control: You can limit the available filter conditions (like equal, lte, gte) using the column.filter.conditionList option.
  
  
• Formula Dependencies: For details on setting up 2-way dependencies between columns, refer to the Row Template example:
  https://paramquery.com/pro/demos/rowtemplate
  

[paramvir]

Upon re-evaluating the built-in capabilities of the grid, there is an even more efficient approach. UTC dates are natively supported by pqGrid, which eliminates the need for complex workarounds.

The Simplified Solution: ISO 8601 Compliance

You do not need to set up two-way column dependencies or manual conversions. By ensuring your data follows standard UTC formatting, the grid handles the heavy lifting automatically.

• UTC Marking: Simply append a 'Z' to your UTC date strings (e.g., "2026-04-26 12:00:00Z"). This explicitly marks them as UTC.
  
• Automatic Conversion: The grid will automatically detect the 'Z' suffix and convert the values to locally formatted dates for both the display and the filtering logic.
  
• Native Formatting: Use the standard column.format option to define how you want the date to appear to the user.
  

Why this is the best approach:

• No Custom Logic: There is no need for column.render or custom filtering comparison callbacks.
  
• Native Performance: It utilizes the grid's internal optimized engine for handling date objects and timezones.
  
• Consistency: The filter and the cell display stay perfectly synchronized with the user's local system time automatically.