Using greater than and less than to filter dates in QGIS

Question

I'm trying to filter some vector data in QGIS. I have a vector data set (points with timestamps and lat/long values in a .csv format. Most of my files can have over 100K points but for this example I am looking at a file with just 200 points). I am currently trying to filter out any data points that are not within two date and time values in QGIS. When importing the .csv file I set the data column header to "Date and Time" for the Date/Time column (screenshot attached)enter image description here.

The problem is when I run a query in the Query Builder to show only points that are after point in time (X) but before point in time (y) it says "The where clause returned 0 rows". So I tried to dumb down my query and just show points that are greater than a certain time but I get the same error.

enter image description here

This is an example of the query I tried running "Time & Date" >= '4/10/2024 11:05:07 (Central Daylight Time)' AND "Time & Date" <= '4/10/2024 11:06:22 (Central Daylight Time)'

The simple query I tried running later. "Time & Date" >= '4/10/2024 11:21:15 (Central Daylight Time)' expecting it to filter out any points of data that are before that time. Neither of these queries worked for me.

I've noticed that when I load a sample of of the data in the Query Builder" that the preview shows it in a different format than what it actually shows up as, if you were to go to the attribute table. It seems that QGIS is adding the time zone when you add the data as a "Date & Time" format.

Format builder

This is what the attribute table looks like when I open it. I feel like the issue might be QGIS adding the time zones but I don't know how to stop that. I've tried removing the time zones by adding a new column of data and copying over the records (but removing the time zone info) but since I save it as a Time & Date column it gets added back.

enter image description here

• Ben W: The format in your query should match the format of the sample values shown in the Query Builder. E.g. "Time & Date" >= '2024年04月10日T11:21:15.000'. You can double-click those sample values to insert them into the query.
• Taras: Please, do not forget about "What should I do when someone answers my question?"

Answer 1

A date or datetime in QGIS is technically always in international date or datetime format (ISO 8601):

• yyyy-MM-dd
• yyyy-MM-ddTHH:mm:ss.sss

Otherwise it is not a date or datetime, but a string representing a date. BUT important to know: what you see in your attribute table or in many other places in QGIS is just a visual representation of your dates or datetimes depending on your chosen or standard locale you can change in your QGIS settings via Settings --> Options --> General. So be aware if it is just a visual representation of a technical date/datetime or an actual string representing a date/datetime.

In your case try this:

"Time & Date" >= '2024-10-04 11:05:07' AND "Time & Date" <= '2024-10-04 11:06:22'

Also good to know is that QGIS is build upon Qt, so for a better understanding you can also always read up Qt documentation:

• https://doc.qt.io/qt-5/qdate.html#fromString
• https://doc.qt.io/qt-5/qtime.html#fromString-1
• https://doc.qt.io/qt-5/qdate.html
• https://doc.qt.io/qt-5/qdatetime.html
• https://doc.qt.io/qt-5/qlocale.html

• Darnell: Thank you sir for your response, it worked great!!!