data field (timestamp) exposed view filter doesn't work

[robertgarrigos]

Description of the bug

Data field, saved as timestamps, cannot get filtered on a view with an exposed date filter.

Steps To Reproduce

To reproduce the behaviour:

1. create a date field (Unix timestamp) in a content type.
2. create a view which shows that content type and field.
3. add an exposed filter for that date field.
4. save the view and try to filter it for a specific date.

Actual behaviour

No content is shown for a specific date

Expected behaviour

List the content with the specific date on the date field

Additional information

Add any other information that could help, such as:

• Backdrop CMS version: 1.25.1

If the date field saves the date as Date or as ISO format, not timestamp, the filter works as expected.

[argiepiano]

I'm not able to reproduce. It's working fine for me. Before filtering:

After filtering:

Some questions:

• What widget are you using?
• What's your field date granularity, in the node, and in the view filter?

[robertgarrigos]

@argiepiano the granularity for both the field and the filter is day. Format d/m/Y. It doesn't matter which widget I use, for either the field or the filter (I tried all of them): it only works when the date is saved as ISO or date, but not as unix timestamp.

[indigoxela]

@robertgarrigos I'm also unable to reproduce, tried the following:

• Content type with a date (timestamp) field
• Created some content
• Created a view with exposed filter on that date field
• Granularity date, input as text
• Only input the date (no time)

Works as expected. (Some notices on php 8.1, though, but those are unrelated) Matching content shows up.

My suspicion is, that your problem has to do with the date input format. Can you try with Y-m-d instead, which is less ambiguous than d/m/Y vs. m/d/Y? Or try to switch month and day in your input.

[indigoxela]

For easier debugging you might want to turn the query display on for views (on /admin/structure/views/settings). That way the query will show up in the views admin UI, including the format.

[robertgarrigos]

This is so weird... I made a video of the whole process to replicate this (https://vimeo.com/845963757). I'm just using a new installation. At the end of the video you can see the versions. In fact, the devel query actually shows the node in table plus, but backdrop doesn't show it. I don't understand what's going on...

[argiepiano]

This is indeed a mystery. I followed the same steps as you did (up to the point in the video where you enable Devel) and the filter is working fine for me. The only difference I can see is that my filter shows up in mm/dd/yyyy format, since I'm in the US, and perhaps that's where the problem resides.

Two things that may help:

1. Post the full URL that includes the query, from the browser navigation. Mine is:

http://bdtesting.localhost:8888/posts?field_my_unique_date_value%5Bvalue%5D%5Bdate%5D=07%2F17%2F2023

2. When indigoxela mentioned the query display for views, she wasn't referring to the Devel full query display. Rather, this is done here: admin/structure/views/settings

Then click "Show information and statistics about the view during live preview" and then "Show SQL query". This results in the following display in the Preview:

[argiepiano]

One more thought. In the setup you recorded, what happens if you choose tomorrow's date or yesterday's date? I'm thinking that there may be some weird timezone adjustment going on.

[robertgarrigos]

1. the url with the query:

   http://backdrop.test/posts?field_date_value%5Bvalue%5D%5Bdate%5D=17%2F07%2F2023

2. and the query:

   SELECT node.`title` AS `node_title`, node.`nid` AS `nid`, node.`created` AS `node_created`, 'node' AS `field_data_field_date_node_entity_type`
   FROM 
   {node} node
   LEFT JOIN {field_data_field_date} field_data_field_date ON node.nid = field_data_field_date.entity_id AND (field_data_field_date.entity_type = 'node' AND field_data_field_date.deleted = '0')
   WHERE (( (`node`.`status` = '1') AND (`node`.`type` IN  ('post')) AND (DATE_FORMAT(FROM_UNIXTIME(field_data_field_date.field_date_value), '%Y-%m-%d') = '2023-07-17') ))
   ORDER BY node_created DESC
   LIMIT 10 OFFSET 0

   The query seems ok, but nothing is shown either in the views admin preview.

         One more thought. In the setup you recorded, what happens if you choose tomorrow's date or yesterday's date? I'm thinking that there may be some weird timezone adjustment going on.

You were right: selecting one day before shows me the node. But this is not right, anyway, I'm working in the same time zone all the time. And deactivating the time zones doesn't fix the problem either.

[argiepiano]

selecting one day before shows me the node. But this is not right

I've seen similar problems like this (where selecting the previous date works) with date filters. I think there is a bug with timezone handling in date views filters.

[robertgarrigos]

I think there is a bug with timezone handling in date views filters.

It looks like ;-)

[indigoxela]

@robertgarrigos can you please share your timezone related setup?

1. /admin/config/regional/settings Regional settings, timezone handling
2. /admin/structure/types/manage/YOURTYPE/fields/YOURFIELD Timezone global settings
3. /admin/reports/status/php PHP default timezone setting

[robertgarrigos]

@indigoxela:

1. Europe/Madrid +0200
2. No timezone global settings shown
3. Europe/Madrid

[indigoxela]

No timezone global settings shown

Can you provide the json of that field instead? (There has to be a setting...)

Go to admin/config/development/configuration/single/export (the Configuration manager export), select Field and your field name. The settings contain tz_handling.

Just to make sure, how to reproduce... :wink:

[robertgarrigos]

Thanks @indigoxela. Starting to find where the problem is: in the config file for that field, the value for the setting tz_handling was none. Changing it to site, the problem is gone. Now we only need to find out why this is saved as none instead of site.

[argiepiano]

I've taken a quick look at the field UI. For any date field type (iso, normal, unix), if you deselect hours and minutes, the time zone handling gets automatically set to none. This is likely the culprit of the discrepancy with the date - probably in the way the views filter handler works with this setting. We should definitely take a closer.

BTW, I'm now remembering this problem with date filtering by date (no time) has been reported tangentially in other issue postings, and I believe, also in the Forum.

[indigoxela]

Starting to find where the problem is: in the config file for that field, the value for the setting tz_handling was none...

Interesting. When adding a new field of type "date", this is usually "Site's time zone". It might be, that at some point in the past you set it to none, or something went weird when upgrading your site from a D7 (if so).

However, this setting is usually in a collapsed fieldset below other field settings on admin/structure/types/manage/NODETYPE/fields/FIELD_NAME.

Here's an example:

Switching that value after content has been created, might lead to unexpected results. Having that set to "none" might also be a bad decision (depends on timezone and date field type).

In general: dates without time (date-only) have several pitfalls, that aren't necessarily bugs.

[argiepiano]

When adding a new field of type "date", this is usually "Site's time zone".

This is not always the case. If you set the date granularity to year, month and day (no time), then Backdrop automatically sets the time zone handling to none

[robertgarrigos]

@indigoxela, this is happening on a fresh backdrop install.

[indigoxela]

If you set the date granularity to year, month and day (no time), then Backdrop automatically sets the time zone handling to none

Ah, that explains it.

this is happening on a fresh backdrop install.

Yes, that's "normal" behavior, I guess.

The question now is, if we could do anything about that. A timestamp (field) always needs a time to store a value. With granularity "day" the time is "guessed". Which leads to "day-slips" in certain timezones. That guessing seems like a workaround for a logical problem. So there's something that site admins have to know and understand, when creating date-only fields.

[robertgarrigos]

I don't think it is a matter of guessing (or it shouldn't be). The query actually returned the right node with the right date, but the view didn't list it.

[robertgarrigos]

The problem is that the timestamp created when saving the date field takes the timezone into account. However, the query which views build does not.

This is the query built by views:

SELECT node.`title` AS `node_title`, 
  node.`nid` AS `nid`, 
  node.`created` AS `node_created`, 
  'node' AS `field_data_field_date_node_entity_type` 
FROM `node` node 
LEFT JOIN `field_data_field_date` field_data_field_date 
ON node.nid = field_data_field_date.entity_id 
AND (field_data_field_date.entity_type = 'node' 
  AND field_data_field_date.deleted = '0') 
WHERE (( (`node`.`status` = '1') 
  AND (`node`.`type` IN ('post')) 
  AND (DATE_FORMAT(FROM_UNIXTIME(field_data_field_date.field_date_value), '%Y-%m-%d') = '2023-07-19') )) 
ORDER BY node_created DESC 
LIMIT 10 OFFSET 0

Here, it is MYSQL who formats the date and is using no timezone (or UTC). This would be the right query, in my timezone (+02:00):

SELECT node.`title` AS `node_title`, 
  node.`nid` AS `nid`, 
  node.`created` AS `node_created`, 
  'node' AS `field_data_field_date_node_entity_type` 
FROM `node` node 
LEFT JOIN `field_data_field_date` field_data_field_date 
ON node.nid = field_data_field_date.entity_id 
AND (field_data_field_date.entity_type = 'node' 
  AND field_data_field_date.deleted = '0') 
WHERE (( (`node`.`status` = '1') 
  AND (`node`.`type` IN ('post')) 
  AND (DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(field_data_field_date.field_date_value), '+00:00', '+02:00'), '%Y-%m-%d') = '2023-07-19') )) 
ORDER BY node_created DESC 
LIMIT 10 OFFSET 0

Provably, the most easy way to fix this is by setting the timezone before making the query. This also gets the proper nodes:

Set time_zone = '+02:00';
SELECT node.`title` AS `node_title`, node.`nid` AS `nid`, node.`created` AS `node_created`, 'node' AS `field_data_field_date_node_entity_type` FROM `node` node LEFT JOIN `field_data_field_date` field_data_field_date ON node.nid = field_data_field_date.entity_id AND (field_data_field_date.entity_type = 'node' AND field_data_field_date.deleted = '0') WHERE (( (`node`.`status` = '1') AND (`node`.`type` IN ('post')) AND (DATE_FORMAT(FROM_UNIXTIME(field_data_field_date.field_date_value), '%Y-%m-%d') = '2023-07-20') )) ORDER BY node_created DESC LIMIT 10 OFFSET 0

I see that this should get handled by set_db_timezone() in modules/date/views/date_sql_handler.inc, but it is setting it as +00:00 with no chance to set it as system time zone. Is there something wrong??

[robertgarrigos]

I believe the problem is in the __construct() function in date_sql_handler.inc:

public function __construct($date_type = DATE_DATETIME, $local_timezone = NULL, $offset = '+00:00') {
    $this->db_type = Database::getConnection()->databaseType();
    $this->date_type = $date_type;
    $this->db_timezone = 'UTC';
    $this->local_timezone = isset($local_timezone) ? $local_timezone : date_default_timezone();
    $this->set_db_timezone($offset);
  }

as $this->set_db_timezone is always $offset (+00:00).