I would like to update query for records between 24 hours since the specific date and time. The current query works fine, except I need to update two timestamps manually. I am looking to reduce timestamps number to one or replace it with dynamic expression, so it will minimize human error if possible.
Current query looks like this:
SELECT timestamp
FROM table
WHERE timestamp BETWEEN '2023-01-18-06.00.00.000000' AND '2023-01-19-06.00.00.000000'
I have been trying multiple recommended options but it does not work yet:
WHERE timestamp > '2023-01-19-06.00.00.000000' - 24 HOURS
WHERE timestamp > '2023-01-19-06.00.00.000000' – ‘24 HOURS’
WHERE timestamp ('2023-01-19-06.00.00.000000' - 24 HOURS)
WHERE timestamp > '2023-01-19-06.00.00.000000' - '24.00.00.000000'
WHERE timestamp BETWEEN '2023-01-04-06.00.00.000000' AND INTERVAL - 24 HOURS
WHERE timestamp > CURRENT DATE - 24 HOURS
WHERE timestamp ('2023-01-19' - 1 DAY, ('06.00.00.000000' - 24 HOURS))
Could anyone let me know what I am doing incorrectly?
'2023-01-19-06.00.00.000000' - 24 HOURS is near, but incorrect because DB2 doesn't see the first value as a timestamp but as a string even if it makes the automatic cast in the working query. so what you have to do is to tell it is a timestamp, because you add a duration
with the timestamp keyword
WHERE yourtimestamp > timestamp '2023-01-19-06.00.00.000000' - 24 HOURS
or the timestamp function
WHERE yourtimestamp > timestamp('2023-01-19-06.00.00.000000') - 24 HOURS
or this notation
WHERE yourtimestamp > '2023-01-19-06.00.00.000000'::timestamp - 24 HOURS
if you're not using DB2LUW or an old version, one or more option may not be available
i suggest you try something like this
SELECT timestamp
FROM table cross join (values timestamp '2023-01-19-06.00.00.000000') as ref (stamp)
WHERE timestamp between ref.stamp - 24 hours and ref.stamp