Solved

Client Script: working with Date/Time

  • 12 October 2020
  • 6 replies
  • 575 views
C
Rank
Userlevel 3
Badge +8

Hello,

Are there any script functions for working with dates and times?

I’d like to work with the date and time as an integer so I can get the difference in hours between two date variables.  I can do Date1 - Date2 but the result is another date.  I’m just wanting the hours.

 

Something like JavaScripts Date.getTime() would be very helpful.

icon

Best answer by rsbisho2 13 October 2020, 19:50

View original

6 replies

R
Rank
Badge +1

Yes, any standard Oracle functions should work.  Date1-date2 should return a number of days by default.  Here are some standard Oracle date function tutorials https://www.tutorialspoint.com/plsql/plsql_date_time.htm

 

If you need to do any work with the IFS calendars, consider looking at the work_time_counter_API.  This API provides methods for calculating numbers of working days between two dates or finding the next valid working day.

D
Rank
Userlevel 4
Badge +10

good info- thx.

I noticed that  TIMESTAMPDIFF() doesn’t work in IFS SQL Query Tool. What would be the equivalent to us in quick reports?

 

durette
Rank
Userlevel 7
Badge +18

TIMESTAMPDIFF is a function in DB2 and MySQL, but it is not valid in Oracle Database.

If you want the difference in hours, take the difference in days and multiply by 24.

(lag_dt - dt) * 24 AS tsd

If you want to ignore minutes and seconds, you can truncate each to their hour before taking this difference.

(TRUNC(lag_dt, 'HH24') - TRUNC(dt, 'HH24')) * 24 AS tsd

 

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
D
Rank
Userlevel 4
Badge +10

Thx, Durette! Good to know.

C
Rank
Userlevel 3
Badge +8

Thanks for the replies.  I stopped getting notifications of replies so I apologize for the delay.

On FSM Mobile the database is SQLite so the function I used was strftime() to get the difference in seconds between two dates. rsbisho2’s reply got me on the right track even though the Oracle functions weren’t available to me.  

 

Thanks again!

alanbclark
Rank
Userlevel 5
Badge +7

Oracle Date and Time

 

Determine the interval breakdown between two dates for a DATE datatype

 

1 SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,

2 TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,

3 trunc(86400*(date2-date1))-

4 60*(trunc((86400*(date2-date1))/60)) seconds,

5 trunc((86400*(date2-date1))/60)-

6 60*(trunc(((86400*(date2-date1))/60)/60)) minutes,

7 trunc(((86400*(date2-date1))/60)/60)-

8 24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,

9 trunc((((86400*(date2-date1))/60)/60)/24) days,

10 trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks

11* FROM date_table

DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS

----------------- ----------------- ---------- ---------- ---------- ---------- ----------

06202003:16:55:14 07082003:11:22:57 43 27 18 17 2

06262003:11:16:36 07082003:11:22:57 21 6 0 12 1

 

Determine the interval breakdown between two dates for a TIMESTAMP datatype

 

1 SELECT time1,

2 time2,

3 substr((time2-time1),instr((time2-time1),' ')+7,2) seconds,

4 substr((time2-time1),instr((time2-time1),' ')+4,2) minutes,

5 substr((time2-time1),instr((time2-time1),' ')+1,2) hours,

6 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))) days,

7 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks

8* FROM date_table

TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS

------------------------- -------------------------- ------- ------- ----- ---- -----

06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2

06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1

 

-Alan

Reply


Terms & ConditionsCookie settings