EPOCH to second conversion

[dkassis]

Hi,

I am trying to convert the below from sql to SAS code in my proc sql statement.  Any ideas?  It seems to not like the  '% 86400' when converting to seconds.  

proc sql;
create table AMI_test as

select lp.epoch_interval_time,

((lp.epoch_interval_time -7*3600) % 86400) div 3600 as intv_hr,
(((lp.epoch_interval_time -7*3600) % 86400) div 3600) +1 as intrvl_24hr_ending

Thanks,

Dan

[ChrisNZ · Accepted solution]

3600 is the number of seconds in an hour, so you are dealing with datetimes (counted in seconds).

So you don't need to convert to seconds.

% is the modulo operator right?

divide ( mod ( lp.epoch_interval_time - 7*3600 , 86400 ), 3600 ) as intv_hr,

should do what you want.

[dkassis]

Thanks, that worked!

[Tom]

What do you think the percent is doing as an operator in that expression?  Is it MOD ?  SAS has a MOD() function.

What are those "magic" numbers?

What is 3600? Is it the number of seconds in an hour?  60sec/min*60min/hr=3,600sec/hr.

If you want the number of seconds in an hour use '01:00:00't

What is 86400?  Is it the number of seconds in a day? 24hr/day*3,600sec/hr=86,400sec/hr

If you want the number of seconds in 24 hours use '24:00:00't

But what is the formula trying to actually do?  Is it subtracting 7 hours? Taking the time since midnight and then hours since midnight?

hour(datepart(intnx('hour',lp.epoch_interval_time,-7,'same')))

[ChrisNZ]

The  mod() takes out all the (full) days, then subtract 7 hours, then transform time to (decimal) day.

I agree that these numbers could be better shown as proper durations such as '01:00:00't  rather than the underlying count.

The intent here is to change unit though, so maybe showing the raw figure makes this intent more obvious. Maybe.

This might be the best balance?

divide ( mod ( lp.epoch_interval_time - '07:00:00't, '24:00:00't), 3600 ) as INTV_HR