Subtract one hour result

I am pulling live data from a db2 server, I am building a Power bi report that shows by time each transaction made. There is a time difference between our server location so the result is one hour ahead. I am trying to subtract one hour from HVTIME (TIME). I can create a custom measure in Power Bi to do this but I am unable to add it to an Axis.

I have tried different SQL statements with no luck.

select

seri.htsern as Serial,
seri.htpart as PartNumber,
seri.htlotn as Batch,
seri.htqty as Quantity,
seri.htstkl as Bin,
seri.htbinl as Location,
serh.hvuser as ReceivedBy,
serh.hvtdat as RecievedDaten,
serh.hvtime as Time

from seri

left join serh on hvsern = htsern


where hvtdat between '2019/10/01' and current date
and hvtrnt = 'R'
and htplnt = 'GC'

The result I am looking for should be 10:04:53 under HVTIME (TIME).

enter image description here

Answer

Does this do what you want?

(serh.hvtime - 1 hour) as Time

You may find this article useful for date/time stuff in DB2.

Advertisement

Answer