Subtract 6 months from EPOCH time

[Nil]

Hi There,

In the database the date and time is stored in EPOCH time. It is 9 long binary type field. It contains date and time. I need to select past 6 months dates only from this file. So how do I subtract past 6 months from this field?

Date example in EPOCH format i.e. stored in database...
1,052,954,260
1,058,206,756
1,058,207,082


Thanks,
NIL

[Barbara Morris]

An EPOCH time is the number of seconds after Jan 1, 1970. So you can convert it to a timestamp by adding seconds to 1970-01-01, do the date/time arithmetic on the timestamp, and then convert it back to an EPOCH time by getting the difference in seconds from 1970-01-01.

      dcl-s epoch_num int(10) inz(1058207082);                  
      dcl-s epoch_ts timestamp;                                    
      dcl-c EPOCH_START z'1970-01-01-00.00.00';    

      // Get the EPOCH time in a timestamp                        
      epoch_ts = EPOCH_START + %seconds(epoch_num);                
      // Subtract 6 months                                        
      epoch_ts -= %months(6);                                      
      // Turn the timestamp back into an EPOCH                    
      epoch_num = %diff(epoch_ts : EPOCH_START : *seconds);        
      return;

If you're using those binary 9 fields in RPG, then I guess you have EXTBININT(*YES) in your H spec. Those are 10 digit numbers, and by default, RPG would only allow them to have 9 digits.

[Nil]

Thanks Barbara, I am trying to convert today's date (12-Sept-2017) to EPOCH so I can I do that?