Report Period conditions use timezone offset for Date field types #4876

New issue

Open

opened 2026-02-20 16:25:31 -05:00 by deekerman · 0 comments

deekerman commented

2026-02-20 16:25:31 -05:00

Owner

Originally created by @ojs87 on GitHub (Oct 26, 2022).

Issue

When a User has set a different timezone and they attempt to report on Date fields, the CRM queries the database using a datetime value with an offset for the timezone. This provides incorrect results as the query will assume a date type has a midnight timestamp.

Expected Behavior

When the the user has set a timezone that is not the same as the database timezone.
The Date Period condition will return the correct results for "Today", "Yesterday" etc

Actual Behavior

When the the user has set a timezone that is not the same as the database timezone.
Date Period conditions of "Yesterday" will return dates from "Today" if the timezone is behind the db timezone.

Possible Fix

If the field type is "datetime" then provide an offset value, else use the midnight value for "date"

Steps to Reproduce

Set your timezone to America/Chicago in your profile.
Create a date field in Contacts
Create a Contacts record with the newly created date field set to today.
Create a Report on Contacts with a condition on the newly created date field equal to the period "Today"
The Contacts record will not be returned in the report

Context

Your Environment

SuiteCRM Version used: SuiteCRM 7.12.7
Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Firefox 105
Environment name and version (e.g. MySQL, PHP 7): PHP7.4
Operating System and version (e.g Ubuntu 16.04): Ubuntu 20.0.5

Originally created by @ojs87 on GitHub (Oct 26, 2022).     #### Issue   When a User has set a different timezone and they attempt to report on Date fields, the CRM queries the database using a datetime value with an offset for the timezone. This provides incorrect results as the query will assume a date type has a midnight timestamp. #### Expected Behavior When the the user has set a timezone that is not the same as the database timezone. The Date Period condition will return the correct results for "Today", "Yesterday" etc #### Actual Behavior   When the the user has set a timezone that is not the same as the database timezone. Date Period conditions of "Yesterday" will return dates from "Today" if the timezone is behind the db timezone. #### Possible Fix  If the field type is "datetime" then provide an offset value, else use the midnight value for "date" #### Steps to Reproduce   1. Set your timezone to America/Chicago in your profile. 2. Create a date field in Contacts 3. Create a Contacts record with the newly created date field set to today. 4. Create a Report on Contacts with a condition on the newly created date field equal to the period "Today" 5. The Contacts record will not be returned in the report #### Context   #### Your Environment  * SuiteCRM Version used: SuiteCRM 7.12.7 * Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Firefox 105 * Environment name and version (e.g. MySQL, PHP 7): PHP7.4 * Operating System and version (e.g Ubuntu 16.04): Ubuntu 20.0.5