Timestamp update DB2 SQL-CodePudding

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?

CodePudding user response：

'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

CodePudding user response：

For the past 24hrs, as implied by your example

WHERE timestamp > CURRENT DATE - 24 HOURS

You'd want to use CURRENT TIMESTAMP not CURRENT DATE

For a specific period, you'll always need two dates specified in the WHERE clause like so
WHERE timestamp BETWEEN startTs AND endTs

For a specific 24hr period from a given starting timestamp, you can do something like so:
WHERE timestamp BETWEEN startTs AND startTs 24 hours

You can define startTs as a global variable, and use it in your select

create variable startTs timestamp default('2023-01-18 06:00:00.000');

SELECT timestamp 
FROM table
WHERE timestamp BETWEEN startTs AND startTs   24 hours;

Or you could use a table value constructor to store it for use...

WITH tmp (startTime) AS (
    VALUES (timestamp('2023-01-19 06:00:00.000'))
) 
select timestamp from table
where timestamp between (select startTime from tmp limit 1) 
                   and (select startTime   2 hours from tmp limit 1);

Depending on your use case, it might be worthwhile to encapsulate the statement as a stored procedure or a user defined table function (UDTF)...