Minute interval in mysql / sas or proc sql

Question

Does any one know proc sql or data step have a function where I can convert datetime value into : 15 mins interval for example :

03NOV2010:00:00:02 --> 0-15
03NOV2010:00:16:02 --> 15-30 

Answer 1

One way is to convert the date to a unix timestamp. A unix timestamp is the number of seconds since January 1st, 1970. Then you can take the modulo of 15*60 to give the number of seconds past the last 15 minute mark.

For example, using the current time now():

select now() - interval unix_timestamp(now())%(15*60) second

This prints 20:00 for me (it's 20:08 here.)

Answer 2

Sounds like you want to apply a format to the minute component of a datetime value.

Using SAS, you could try something like:

proc format;
  value min15int
    00-15 = "00-15"
    15<-30= "16-30"
    30<-45= "31-45"
    45<-60= "46-60";
run;

data test;
  a='03NOV2010:00:00:02'dt;output;
  a='03NOV2010:00:16:02'dt;output;
run;

data test2;
  set test;
    b=minute(a);
    c=b;
    format c min15int.;
run;

This is just quick and dirty, you can adapt it to your requirements. The principle is that we are just creating a display format to show a value with a certain format applied to it. I've separately created the variable 'b', which is just the minute component of the datetime, then in variable 'c' I'm applying the format.

hth.