Error converting a string date/time to DateTime apparently related to Daylight Saving Time

[PedroVenancio]

What is the bug or the crash?

I was trying to convert a string field containing date and time information, to a Date&Time field with the expression:

to_datetime('1994-03-27 00:00:00','yyyy-MM-dd hh:mm:ss')

It worked well until it find a specific date/time:

to_datetime('1994-03-27 01:00:00','yyyy-MM-dd hh:mm:ss')

Eval Error: Cannot convert '1994-03-27 01:00:00' to DateTime

After some tests I've found that, for instance, this conversions work as expected:

to_datetime('1994-03-27 00:00:00','yyyy-MM-dd hh:mm:ss')
to_datetime('1994-03-27 02:00:00','yyyy-MM-dd hh:mm:ss')
to_datetime('1994-03-26 01:00:00','yyyy-MM-dd hh:mm:ss')
to_datetime('1994-03-28 01:00:00','yyyy-MM-dd hh:mm:ss')

But those does not work and give the Eval Error:

to_datetime('1994-03-27 01:00:00','yyyy-MM-dd hh:mm:ss')
to_datetime('2022-03-27 01:00:00','yyyy-MM-dd hh:mm:ss')
to_datetime('2024-03-31 01:00:00','yyyy-MM-dd hh:mm:ss')

The relation between '1994-03-27 01:00:00', '2022-03-27 01:00:00' and '2024-03-31 01:00:00' is that corresponds to the hour when the change between Winter Time to Summer Time occurs, and the 01:00:00 becomes 02:00:00:
https://en.wikipedia.org/wiki/Summer_time_in_Europe

I'm in Portugal, with Western European Time / Greenwich Mean Time.

I've tested on Windows, with QGIS 3.28, 3.34, 3.36 and 3.38, and all of them have this issue.

I've tried to change the Locale from

to

without success.

In Linux the error does not occurs and everything works fine.

Steps to reproduce the issue

To test, simply open the Field Calculator and paste the expression:

to_datetime('1994-03-27 01:00:00','yyyy-MM-dd hh:mm:ss')

Versions

<style type="text/css"> p, li { white-space: pre-wrap; } </style>

QGIS version	3.38.3-Grenoble	QGIS code revision	37f9e6e
Qt version	5.15.13
Python version	3.12.6
GDAL/OGR version	3.9.2
PROJ version	9.4.0
EPSG Registry database version	v11.004 (2024-02-24)
GEOS version	3.12.2-CAPI-1.18.2
SQLite version	3.45.1
PDAL version	2.6.3
PostgreSQL client version	16.2
SpatiaLite version	5.1.0
QWT version	6.2.0
QScintilla2 version	2.14.1
OS version	Windows 11 Version 2009
Active Python plugins
a00_qpip	1.0
active_fire	0.3
AemetOpenDataDownloader	0.1
attributeBasedClustering	2.2.1
BackupLayer	0.3
buffer_without_overlaps	0.5
cartography_tools	1.2.1
clusterpoints	6.1
ContrastHomogenizer	3.2
crayfish	3.6.0
d3datavis	3.1.1
DataPlotly	4.2.0
densityanalysis	2024.8.28
downloadgeostatportugal	0.2
dzetsaka	3.70
edr_plugin	1.0.0
export_to_sql_server	0.5
FreehandRasterGeoreferencer	0.8.3
geocatbridge	4.5.1
GeoCoding	2.19
getools	1.0.0
go2streetview	10.0
gribdownloader	0.5
GroupStats	2.2.7
HCMGIS	24.10.20
Hqgis	1.2.3
inspireatomclient	0.8.1
kmltools	3.2.2
layer2kmz-processing	2.0.0
LecoS	3.0.1
loadthemall	3.4
los_tools	1.1.2
lrs	1.2.3
mapswipetool_plugin	1.3
MultiDistanceBuffer	3.2.4
nominatim_locator_filter	0.3.2
ntv2_transformations	0.20
orfeoToolbox_provider	3.0.3
OSMDownloader	1.0.3
osm_parser	0.0.1
photo2shape	3.0
PluginLoadTimes	4.1.0
pointsamplingtool	0.5.4
postgis_geoprocessing	0.9
PotentialSlopeFailure	0.7
processing_gnm	0.2.0
processing_saga_nextgen	1.0.0
ProcessX	1.7.1
profiletool	4.2.6
qchainage	3.0.1
qconsolidate	1.1.0
qfieldsync	v4.11.0
qgis-maptiler-plugin	3.3.2
Qgis2threejs	2.8
qgis2web	3.23.0
QGIS_Dashboard-master	0.1
qgis_stac	1.1.2
QNEAT3	1.0.5
QuickWebViewer	0.2.1
QuickWKT	3.1
quick_map_services	0.19.34
raster_cutter	0.6.1
refFunctions	1.6
ReverseFlow	0.2
road_slope_calculator	0.5
scipy_filters	1.4
SemiAutomaticClassificationPlugin	8.4.0
SentinelHub	2.0.2
simplesvg	3.0.3
simplewcs2	0.3
SpreadsheetLayers	2.1.2
SRTM-Downloader	3.2.3
statist	3.2
SwapVectorDirection	0.9
TerrainShading	0.9.5
timemanager	3.6
timezone_expressions	v0.2
valuetool	3.0.19
Video_UAV_Tracker	2.1
ViewshedAnalysis	1.9
VoGisProfilTool	3.0.2
whitebox_workflows_for_qgis	1.2.6
db_manager	0.1.20
grassprovider	2.12.99
MetaSearch	0.3.6
processing	2.12.99
mmqgis	2024.11.8

Supported QGIS version

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS profile

Additional context

No response

[agiudiceandrea]

@PedroVenancio, on Windows, it looks like it doesn't depend on the locale set in Settings->Options->General->Locale, but on the time zone set for the OS.

In my Windows 10 system in Italy, with the OS time zone set to UTC+01:00, the expression to_datetime('1994-03-27 01:00:00','yyyy-MM-dd hh:mm:ss') returns <datetime: 1994-03-27 01:00:00 (ora solare Europa occidentale)>, while to_datetime('1994-03-27 02:00:00','yyyy-MM-dd hh:mm:ss') is invalid.

If I set the OS time zone to UTC+00:00 (like in Portugal), then the expression to_datetime('1994-03-27 01:00:00','yyyy-MM-dd hh:mm:ss') is invalid, while to_datetime('1994-03-27 02:00:00','yyyy-MM-dd hh:mm:ss') returns <datetime: 1994-03-27 02:00:00 (ora legale GMT)>.

The same happens using the QLocale().toDateTime() Qt function in the Python console, which is used by the to_datetime QGIS function to perform the calculation.
QLocale().toDateTime( '1994-03-27 01:00:00','yyyy-MM-dd hh:mm:ss' ) returns PyQt5.QtCore.QDateTime(1994, 3, 27, 1, 0) when the OS time zone is set to UTC+01:00, while it returns PyQt5.QtCore.QDateTime() when the OS time zone is set to UTC+00:00.