Extracting data based on time range-- inrange command not capturing observations

[Mich Pan]

Hi everyone,

My dataset includes information on when individuals tap/log in. I would like to work with a subset of my dataset, based on exactly when these individuals are captured tapping in. The time range of interest is 05:45- 09:00 am.

I am using Stata 16.1

I have tried following William's suggestion in this post https://www.statalist.org/forums/for...ates-and-times.

For some reason, the following code spits out the variable "keepthis" that is full of 0's, no "1"s:

gen entry_time = entry_datetime 
format entry_time %tcHH:MM:SS.sss // time works 
generate keepthis = inrange(entry_time, hms(05,45,00), hms(09,00,00))

I can't figure out why! Any insights are appreciated.



Dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input float id double entry_datetime float entry_date byte trnspt_mode_cd
  1 1885557758000 21823 1
  2 1885562330000 21823 1
  3 1885577749000 21823 1
  4 1885574952000 21823 1
  5 1885539516000 21823 2
  6 1885548725000 21823 1
  7 1885532387000 21823 2
  8 1885569476000 21823 1
  9 1885569271000 21823 1
 10 1885554651000 21823 1
 11 1885532271000 21823 2
 12 1885585196000 21823 2
 13 1885532278000 21823 2
 14 1885555809000 21823 1
 15 1885530208000 21823 1
 16 1885549637000 21823 1
 17 1885555058000 21823 1
 18 1885555320000 21823 1
 19 1.8855547e+12 21823 2
 20 1885573609000 21823 1
 21 1885549879000 21823 1
 22 1885531646000 21823 1
 23 1885569754000 21823 1
 24 1885544420000 21823 1
 25 1885530270000 21823 1
 26 1885548842000 21823 1
 27 1885556195000 21823 2
 28 1885590028000 21823 1
 29 1885544083000 21823 1
 30 1885565447000 21823 1
 31 1885566394000 21823 1
 32 1885540579000 21823 1
 33 1885564446000 21823 1
 34 1885532403000 21823 1
 35 1885530818000 21823 1
 36 1885561773000 21823 2
 37 1885582243000 21823 2
 38 1885571226000 21823 1
 39 1885560217000 21823 2
 40 1885531861000 21823 1
 41 1885567292000 21823 1
 42 1885531512000 21823 1
 43 1885547268000 21823 1
 44 1885563392000 21823 2
 45 1885555799000 21823 1
 46 1885563568000 21823 1
 47 1885532636000 21823 1
 48 1885534353000 21823 1
 49 1885531753000 21823 1
 50 1885572935000 21823 2
 51 1885568129000 21823 1
 52 1885539278000 21823 1
 53 1885560787000 21823 1
 54 1885569176000 21823 1
 55 1885554294000 21823 1
 56 1885533165000 21823 2
 57 1885564383000 21823 1
 58 1885579774000 21823 2
 59 1885571127000 21823 2
 60 1885555232000 21823 1
 61 1885549333000 21823 1
 62 1885566967000 21823 1
 63 1885581575000 21823 1
 64 1885563490000 21823 2
 65 1885533439000 21823 1
 66 1885576905000 21823 2
 67 1885553424000 21823 1
 68 1885561123000 21823 2
 69 1885531762000 21823 1
 70 1885573248000 21823 2
 71 1885566690000 21823 1
 72 1885544266000 21823 2
 73 1885571062000 21823 1
 74 1885553014000 21823 1
 75 1885568505000 21823 2
 76 1885585260000 21823 2
 77 1885582032000 21823 1
 78 1885555847000 21823 1
 79 1885567347000 21823 2
 80 1885558674000 21823 1
 81 1885545412000 21823 1
 82 1885568668000 21823 2
 83 1885567224000 21823 1
 84 1885531774000 21823 2
 85 1885573059000 21823 2
 86 1885551918000 21823 1
 87 1885530128000 21823 1
 88 1885544421000 21823 1
 89 1885564805000 21823 1
 90 1885550584000 21823 2
 91 1885529413000 21823 1
 92 1885529952000 21823 2
 93 1885532724000 21823 1
 94 1885546429000 21823 2
 95 1885533333000 21823 1
 96 1885535146000 21823 1
 97 1885532395000 21823 1
 98 1885533379000 21823 2
 99 1885580630000 21823 2
100 1885559297000 21823 1
end
format %tcDay_Mon_DD_CCYY_HH:MM:SS entry_datetime
format %td entry_date

[Nick Cox]

Your entry date-times are values like

. di %tc 1885557758000
01oct2019 14:02:38

whereas you're asking for values in this range

. di %tc  hms(05,45,00) " "  %tc  hms(09,00,00)
01jan1960 05:45:00 01jan1960 09:00:00

which yields no catches.

The intent of your code is that you want to ignore daily date and focus only on time of day.

But setting the display format of a date-time variable to

  %tcHH:MM:SS.sss 

means only that that part of the date-time -- the time of day -- is what you see. The value of the date-time held is completely unchanged. That syntax doesn't convert the date-time to a time-of-day variable. This is a common misunderstanding. See e.g. https://journals.sagepub.com/doi/pdf...867X1201200415 There are various ways to do the conversion. One is to subtract the date-time at the start of the day.

* Example generated by -dataex-. For more info, type help dataex
clear
input float id double entry_datetime float entry_date byte trnspt_mode_cd
  1 1885557758000 21823 1
  2 1885562330000 21823 1
  3 1885577749000 21823 1
  4 1885574952000 21823 1
  5 1885539516000 21823 2
  6 1885548725000 21823 1
  7 1885532387000 21823 2
  8 1885569476000 21823 1
  9 1885569271000 21823 1
 10 1885554651000 21823 1
 11 1885532271000 21823 2
 12 1885585196000 21823 2
 13 1885532278000 21823 2
 14 1885555809000 21823 1
 15 1885530208000 21823 1
 16 1885549637000 21823 1
 17 1885555058000 21823 1
 18 1885555320000 21823 1
 19 1.8855547e+12 21823 2
 20 1885573609000 21823 1
 21 1885549879000 21823 1
 22 1885531646000 21823 1
 23 1885569754000 21823 1
 24 1885544420000 21823 1
 25 1885530270000 21823 1
 26 1885548842000 21823 1
 27 1885556195000 21823 2
 28 1885590028000 21823 1
 29 1885544083000 21823 1
 30 1885565447000 21823 1
 31 1885566394000 21823 1
 32 1885540579000 21823 1
 33 1885564446000 21823 1
 34 1885532403000 21823 1
 35 1885530818000 21823 1
 36 1885561773000 21823 2
 37 1885582243000 21823 2
 38 1885571226000 21823 1
 39 1885560217000 21823 2
 40 1885531861000 21823 1
 41 1885567292000 21823 1
 42 1885531512000 21823 1
 43 1885547268000 21823 1
 44 1885563392000 21823 2
 45 1885555799000 21823 1
 46 1885563568000 21823 1
 47 1885532636000 21823 1
 48 1885534353000 21823 1
 49 1885531753000 21823 1
 50 1885572935000 21823 2
 51 1885568129000 21823 1
 52 1885539278000 21823 1
 53 1885560787000 21823 1
 54 1885569176000 21823 1
 55 1885554294000 21823 1
 56 1885533165000 21823 2
 57 1885564383000 21823 1
 58 1885579774000 21823 2
 59 1885571127000 21823 2
 60 1885555232000 21823 1
 61 1885549333000 21823 1
 62 1885566967000 21823 1
 63 1885581575000 21823 1
 64 1885563490000 21823 2
 65 1885533439000 21823 1
 66 1885576905000 21823 2
 67 1885553424000 21823 1
 68 1885561123000 21823 2
 69 1885531762000 21823 1
 70 1885573248000 21823 2
 71 1885566690000 21823 1
 72 1885544266000 21823 2
 73 1885571062000 21823 1
 74 1885553014000 21823 1
 75 1885568505000 21823 2
 76 1885585260000 21823 2
 77 1885582032000 21823 1
 78 1885555847000 21823 1
 79 1885567347000 21823 2
 80 1885558674000 21823 1
 81 1885545412000 21823 1
 82 1885568668000 21823 2
 83 1885567224000 21823 1
 84 1885531774000 21823 2
 85 1885573059000 21823 2
 86 1885551918000 21823 1
 87 1885530128000 21823 1
 88 1885544421000 21823 1
 89 1885564805000 21823 1
 90 1885550584000 21823 2
 91 1885529413000 21823 1
 92 1885529952000 21823 2
 93 1885532724000 21823 1
 94 1885546429000 21823 2
 95 1885533333000 21823 1
 96 1885535146000 21823 1
 97 1885532395000 21823 1
 98 1885533379000 21823 2
 99 1885580630000 21823 2
100 1885559297000 21823 1
end
format %tcDay_Mon_DD_CCYY_HH:MM:SS entry_datetime
format %td entry_date

gen double entry_time = entry_datetime - cofd(entry_date)

format entry_time

su entry_time, meanonly
di "min:" %tcHH:MM:SS r(min)
di "max:" %tcHH:MM:SS r(max)

The data example shows

. di "min:" %tcHH:MM:SS r(min)
min:06:10:13

. di "max:" %tcHH:MM:SS r(max)
max:23:00:28

EDIT The post linked in #1 does exactly the same thing in a different way, namely subtract

 
 dhms(date,0,0,0)

which for your dataset would be

 
 dhms(entry_date,0,0,0)

[Mich Pan]

oh wow, this went way over my head. Thanks so much Nick for the thorough explanation.

The code:

    gen double entry_time = entry_datetime - cofd(entry_date)
        format entry_time

    su entry_time, meanonly

    generate keepthis = inrange(entry_time, hms(05,45,00), hms(09,00,00))     
    
drop if keepthis == 0

worked perfectly! Thanks again.

[Nick Cox]

In my code

 
 format entry_time

should be

  
 format entry_time %tcHH:MM:SS

or whatever else you want.