IBM iSeries Combine Date & Time decimal fields to compare current time
I'm working in IBM iSeries where a date and time are stored as decimal, 8 & 6 characters respectively, ('YYYYMMDD') & 123456
I have been able to derive the current date and time in separate columns. And then the latest date and time of two time stamps which are variable with a second date and time recorded only if a record was acted on more than once.
I'm struggling with comparing the combined latest date/time stamp to the current time to determine the amount of time passed (preferably in minutes) with stamps commonly moving past midnight.
Select (SELECT current date FROM sysibm.sysdummy1) "Current Date", (SELECT current time FROM sysibm.sysdummy1) "Current Time",
char(date(
substr(MAX(Date1,Date2),1,4) || '-'|| substr(MAX(Date1,Date2),5,2) || '-'|| substr(MAX(Date1,Date2),7,2)), USA) "Last View Date",
Case When MAX(Date1,Date2)=Date2 then Time2/86400 else Time1/86400 end "Last Time"
from .....
You need to convert your date and time numeric fields into a single timestamp field. The timestamp format for our purposes is "YYYY-MM-DD-hh.mm.ss".
Select timestamp(substr(digits(date1),1,4) ||'-'||
substr(digits(date1),5,2) ||'-'||
substr(digits(date1),7,2) ||'-'||
substr(digits(time1),1,2) ||'.'||
substr(digits(time1),3,2) ||'.'||
substr(digits(time1),5,2)) as Timestamp1
from ...
Note the use of DIGITS instead of CHAR so we get the leading zeros.
Then you can use the TimestampDiff() function to determine the number of minutes between the two. The TimestampDiff() function takes two parameters. The first is an integer that tells it which time unit you need (seconds, minutes, hours, days, etc). The second is a subtraction of the two dates converted to character. I don't know why it can't just take two timestamps, but that's how IBM gave it to us. Here's an example.
Select TimestampDiff(4, Char(Current_Timestamp-
timestamp(substr(digits(date1),1,4) ||'-'||
substr(digits(date1),5,2) ||'-'||
substr(digits(date1),7,2) ||'-'||
substr(digits(time1),1,2) ||'.'||
substr(digits(time1),3,2) ||'.'||
substr(digits(time1),5,2)))) as HoursDiff
from ...
This will, of course, handle any past-midnight date rollover. The values for the first parameter in TimestampDiff() are:
1 = Microseconds
2 = Seconds
4 = Minutes
8 = Hours
16 = Days
32 = Weeks
64 = Months
128 = Years
上一篇: 2次（）值之间的差异