Hello!
I got a Named query with a timestamp
...
WHERE TM.timestamp BETWEEN ADD_MONTHS(TO_DATE(:startdate, 'YYYY-MM-DD HH24:MI:SS'), -12) AND TO_DATE(:startdate, 'YYYY-MM-DD HH24:MI:SS')
...
WHERE TO_CHAR(TM.timestamp, 'YYYY') = TO_CHAR(TO_DATE(:startdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY')
AND TO_CHAR(TM.timestamp, 'IW') = TO_CHAR(TO_DATE(:startdate, 'YYYY-MM-DD HH24:MI:SS'), 'IW')
AND TS.ORDEN_DNF IS NOT NULL
....
2023-01-01
when I want to execute the query and use the parameter startdate
as string it shows data, if I use Data Type DateTime, doesnt
...
WHERE TO_CHAR(TM.timestamp, 'YYYY') = TO_CHAR(TO_DATE(:startdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY')
AND TO_CHAR(TM.timestamp, 'IW') = TO_CHAR(TO_DATE(:startdate, 'YYYY-MM-DD HH24:MI:SS'), 'IW')
AND TS.ORDEN_DNF IS NOT NULL
....
This sort of thing will slow down your database queries. The query engine has to convert the timestamp column of every record in the table before it can do the comparison.
If the column datatype is set to DateTime then it can be indexed and searching by non-converted timestamp will be much, much faster.
Go to your database utility and check the datatype for the timestamp column. It should be DATETIME or BIGINT if using Ignition's t_stamp format. Don't use string for dates except at the last moment before display.
1 Like
