Troubleshooting
Problem
This document explains how to use Query for i (Query/400) to calculate the difference between two time fields.
Resolving The Problem
The following is a Quer for i example of calculating the difference between two time fields stored as six-position numeric fields in HHMMSS format:
Query . . . . . . . . . . . . . . . . . QRYTIME2
Library . . . . . . . . . . . . . . . QRYLIB
Query text . . . . . . . . . . . . . . Calculating difference in time
Selected files
ID File Library Member Record Format
T01 TIMES QRYLIB *FIRST TIMER
Result fields
Name Expression Column Heading Len
STIMEX digits(time1)
STIMEY substr(stimex,1,2) || ':' ||
substr(stimex,3,2) || ':' ||
substr(stimex,5,2)
STIME time(stimey)
ETIMEX digits(time2)
ETIMEY substr(etimex,1,2) || ':' ||
substr(etimex,3,2) || ':' ||
substr(etimex,5,2)
ETIME time(etimey)
DTIME etime-stime
Ordering of selected fields
Field Sort Ascending/ Break Field
Name Priority Descending Level Text
TIME1
STIME
TIME2
ETIME
DTIME
Results of QRYTIME2:
TIME1 STIME TIME2 ETIME DTIME
000001 95,651 09:56:51 95,821 09:58:21 0:01:30
000002 134,815 13:48:15 152,154 15:21:54 1:33:39
Query . . . . . . . . . . . . . . . . . QRYTIME2
Library . . . . . . . . . . . . . . . QRYLIB
Query text . . . . . . . . . . . . . . Calculating difference in time
Selected files
ID File Library Member Record Format
T01 TIMES QRYLIB *FIRST TIMER
Result fields
Name Expression Column Heading Len
STIMEX digits(time1)
STIMEY substr(stimex,1,2) || ':' ||
substr(stimex,3,2) || ':' ||
substr(stimex,5,2)
STIME time(stimey)
ETIMEX digits(time2)
ETIMEY substr(etimex,1,2) || ':' ||
substr(etimex,3,2) || ':' ||
substr(etimex,5,2)
ETIME time(etimey)
DTIME etime-stime
Ordering of selected fields
Field Sort Ascending/ Break Field
Name Priority Descending Level Text
TIME1
STIME
TIME2
ETIME
DTIME
Results of QRYTIME2:
TIME1 STIME TIME2 ETIME DTIME
000001 95,651 09:56:51 95,821 09:58:21 0:01:30
000002 134,815 13:48:15 152,154 15:21:54 1:33:39
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CQbAAM","label":"IBM i Db2-\u003EQuery\/400"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
6511956
Was this topic helpful?
Document Information
Modified date:
30 November 2024
UID
nas8N1010686