Using replace if with date and time variable

[Carlos Macuada]

Hi,

I am working with a data set with timestamps of when certain work was done (see dataex below). As you can see, I have several entries for same IDs, since I need to identify if work was done on the following blocks:

Sunday thru Friday from 6pm to 9pm
Saturday and Sunday from 9am to 6pm

To do so, I have created several variables, such as timedate_PT (date and time work was done), and dayofweek used to extract the day (0 to 6). How can I gen a variable that identifies this situations?

I thought about something like:

replace aux = 1 if timedate_PT > tc(9:00) & timedate_PT < tc(18:00) & r_day == 1 // Identify cases between 9am and 6pm, in a weekend.
(0 real changes made)

Of course in that syntax I am using a timedate variable ignoring the date part (tc only states time and no date), reason why I guess it does not work

I have read the timedate documentation to try to solve my problem, but have not been able to do so, reason why I post here for the first time.

Thanks,
Carlos

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 date_text str8 time_text str18 datetime_text float(timedate_ET timedate_PT dayofweek day r_day id)
"03oct2018" "18:42:52" "03oct2018 18:42:52" 1.8542114e+12 1.8542006e+12 21460 3 0  1
"12oct2018" "21:55:55" "12oct2018 21:55:55" 1.8550005e+12 1.8549898e+12 21469 5 0  1
"13oct2018" "21:53:28" "13oct2018 21:53:28" 1.8550868e+12  1.855076e+12 21470 6 1  1
"14oct2018" "13:26:46" "14oct2018 13:26:46" 1.8551428e+12  1.855132e+12 21471 0 1  1
"15oct2018" "22:55:05" "15oct2018 22:55:05" 1.8552633e+12 1.8552526e+12 21472 1 0  1
"16oct2018" "19:51:09" "16oct2018 19:51:09" 1.8553387e+12  1.855328e+12 21473 2 0  1
"18oct2018" "19:15:57" "18oct2018 19:15:57" 1.8555094e+12 1.8554986e+12 21475 4 0  1
"19oct2018" "15:09:21" "19oct2018 15:09:21"  1.855581e+12   1.85557e+12 21476 5 0  1
"21oct2018" "21:46:25" "21oct2018 21:46:25" 1.8557775e+12 1.8557668e+12 21478 0 1  1
"22oct2018" "19:27:51" "22oct2018 19:27:51" 1.8558557e+12  1.855845e+12 21479 1 0  1
"01oct2018" "10:08:00" "01oct2018 10:08:00" 1.8540077e+12  1.853997e+12 21458 1 0  2
"01oct2018" "19:01:37" "01oct2018 19:01:37" 1.8540397e+12  1.854029e+12 21458 1 0  2
"03oct2018" "19:14:12" "03oct2018 19:14:12"  1.854213e+12 1.8542024e+12 21460 3 0  2
"04oct2018" "19:37:02" "04oct2018 19:37:02"  1.854301e+12 1.8542903e+12 21461 4 0  2
"05oct2018" "19:37:36" "05oct2018 19:37:36" 1.8543875e+12 1.8543768e+12 21462 5 0  2
"05oct2018" "20:30:33" "05oct2018 20:30:33" 1.8543907e+12   1.85438e+12 21462 5 0  2
"06oct2018" "08:34:19" "06oct2018 08:34:19"  1.854434e+12 1.8544233e+12 21463 6 1  2
"06oct2018" "09:01:53" "06oct2018 09:01:53" 1.8544358e+12  1.854425e+12 21463 6 1  2
"06oct2018" "20:13:49" "06oct2018 20:13:49"  1.854476e+12 1.8544652e+12 21463 6 1  2
"08oct2018" "09:30:15" "08oct2018 09:30:15" 1.8546102e+12 1.8545995e+12 21465 1 0  2
"08oct2018" "18:48:25" "08oct2018 18:48:25" 1.8546438e+12  1.854633e+12 21465 1 0  2
"08oct2018" "19:58:22" "08oct2018 19:58:22"  1.854648e+12 1.8546372e+12 21465 1 0  2
"08oct2018" "20:22:00" "08oct2018 20:22:00" 1.8546493e+12 1.8546385e+12 21465 1 0  2
"29sep2018" "17:03:57" "29sep2018 17:03:57"   1.85386e+12  1.853849e+12 21456 6 1  3
"03oct2018" "18:50:05" "03oct2018 18:50:05" 1.8542118e+12  1.854201e+12 21460 3 0  3
"12oct2018" "13:55:46" "12oct2018 13:55:46" 1.8549717e+12  1.854961e+12 21469 5 0  3
"13oct2018" "15:17:10" "13oct2018 15:17:10"  1.855063e+12 1.8550523e+12 21470 6 1  3
"15oct2018" "11:14:47" "15oct2018 11:14:47" 1.8552213e+12 1.8552105e+12 21472 1 0  3
"19oct2018" "17:36:54" "19oct2018 17:36:54"   1.85559e+12  1.855579e+12 21476 5 0  3
"22oct2018" "19:22:26" "22oct2018 19:22:26" 1.8558554e+12 1.8558446e+12 21479 1 0  3
"01oct2018" "12:59:09" "01oct2018 12:59:09"  1.854018e+12  1.854007e+12 21458 1 0  4
"01oct2018" "15:00:43" "01oct2018 15:00:43" 1.8540252e+12 1.8540145e+12 21458 1 0  4
"03oct2018" "18:51:00" "03oct2018 18:51:00"  1.854212e+12  1.854201e+12 21460 3 0  4
"01oct2018" "15:31:00" "01oct2018 15:31:00"  1.854027e+12 1.8540163e+12 21458 1 0  5
"29sep2018" "13:22:12" "29sep2018 13:22:12" 1.8538466e+12  1.853836e+12 21456 6 1  6
"29sep2018" "14:52:20" "29sep2018 14:52:20"  1.853852e+12 1.8538412e+12 21456 6 1  6
"03oct2018" "19:25:41" "03oct2018 19:25:41"  1.854214e+12 1.8542032e+12 21460 3 0  6
"10oct2018" "17:33:44" "10oct2018 17:33:44"  1.854812e+12 1.8548013e+12 21467 3 0  6
"10oct2018" "20:33:09" "10oct2018 20:33:09"  1.854823e+12  1.854812e+12 21467 3 0  6
"12oct2018" "14:24:13" "12oct2018 14:24:13" 1.8549734e+12 1.8549627e+12 21469 5 0  6
"14oct2018" "14:22:58" "14oct2018 14:22:58"  1.855146e+12 1.8551354e+12 21471 0 1  6
"01oct2018" "15:43:00" "01oct2018 15:43:00" 1.8540277e+12  1.854017e+12 21458 1 0  7
"01oct2018" "13:30:38" "01oct2018 13:30:38"   1.85402e+12  1.854009e+12 21458 1 0  8
"08oct2018" "16:04:14" "08oct2018 16:04:14"  1.854634e+12  1.854623e+12 21465 1 0  8
"09oct2018" "18:32:10" "09oct2018 18:32:10"  1.854729e+12 1.8547183e+12 21466 2 0  8
"11oct2018" "10:06:29" "11oct2018 10:06:29" 1.8548716e+12  1.854861e+12 21468 4 0  8
"19oct2018" "17:40:22" "19oct2018 17:40:22"   1.85559e+12 1.8555792e+12 21476 5 0  8
"21oct2018" "15:37:11" "21oct2018 15:37:11" 1.8557554e+12 1.8557446e+12 21478 0 1  8
"22oct2018" "19:11:36" "22oct2018 19:11:36" 1.8558547e+12  1.855844e+12 21479 1 0  8
"03oct2018" "18:45:43" "03oct2018 18:45:43" 1.8542115e+12 1.8542007e+12 21460 3 0  9
"03oct2018" "18:47:08" "03oct2018 18:47:08" 1.8542116e+12  1.854201e+12 21460 3 0  9
"05oct2018" "21:40:59" "05oct2018 21:40:59"  1.854395e+12  1.854384e+12 21462 5 0  9
"07oct2018" "13:59:28" "07oct2018 13:59:28"   1.85454e+12 1.8545292e+12 21464 0 1  9
"08oct2018" "00:30:07" "08oct2018 00:30:07"  1.854578e+12  1.854567e+12 21464 0 1  9
"08oct2018" "23:09:00" "08oct2018 23:09:00" 1.8546594e+12 1.8546486e+12 21465 1 0  9
"01oct2018" "13:33:53" "01oct2018 13:33:53"   1.85402e+12 1.8540092e+12 21458 1 0 10
"01oct2018" "19:49:11" "01oct2018 19:49:11" 1.8540425e+12 1.8540318e+12 21458 1 0 10
"08oct2018" "16:07:00" "08oct2018 16:07:00"  1.854634e+12 1.8546233e+12 21465 1 0 10
"29sep2018" "13:51:00" "29sep2018 13:51:00" 1.8538483e+12 1.8538375e+12 21456 6 1 11
"01oct2018" "15:53:06" "01oct2018 15:53:06" 1.8540284e+12 1.8540176e+12 21458 1 0 12
"03oct2018" "18:57:57" "03oct2018 18:57:57" 1.8542123e+12 1.8542015e+12 21460 3 0 12
"04oct2018" "16:33:00" "04oct2018 16:33:00"   1.85429e+12 1.8542793e+12 21461 4 0 12
"03oct2018" "18:49:00" "03oct2018 18:49:00" 1.8542118e+12  1.854201e+12 21460 3 0 13
"03oct2018" "19:08:00" "03oct2018 19:08:00"  1.854213e+12  1.854202e+12 21460 3 0 14
"01oct2018" "10:12:00" "01oct2018 10:12:00"  1.854008e+12  1.853997e+12 21458 1 0 15
"01oct2018" "19:04:00" "01oct2018 19:04:00" 1.8540398e+12  1.854029e+12 21458 1 0 15
"01oct2018" "10:15:00" "01oct2018 10:15:00"  1.854008e+12 1.8539973e+12 21458 1 0 16
"01oct2018" "19:12:49" "01oct2018 19:12:49" 1.8540403e+12 1.8540296e+12 21458 1 0 16
"03oct2018" "19:17:11" "03oct2018 19:17:11" 1.8542135e+12 1.8542027e+12 21460 3 0 16
"04oct2018" "19:40:13" "04oct2018 19:40:13" 1.8543013e+12 1.8542905e+12 21461 4 0 16
"05oct2018" "20:32:11" "05oct2018 20:32:11" 1.8543908e+12   1.85438e+12 21462 5 0 16
"06oct2018" "08:38:54" "06oct2018 08:38:54" 1.8544343e+12 1.8544236e+12 21463 6 1 16
"08oct2018" "09:32:59" "08oct2018 09:32:59" 1.8546103e+12 1.8545996e+12 21465 1 0 16
"03oct2018" "19:45:37" "03oct2018 19:45:37"  1.854215e+12 1.8542044e+12 21460 3 0 17
"05oct2018" "21:44:53" "05oct2018 21:44:53"  1.854395e+12 1.8543844e+12 21462 5 0 17
"07oct2018" "14:00:42" "07oct2018 14:00:42"   1.85454e+12 1.8545293e+12 21464 0 1 17
"08oct2018" "00:26:54" "08oct2018 00:26:54" 1.8545776e+12  1.854567e+12 21464 0 1 17
"01oct2018" "10:19:00" "01oct2018 10:19:00" 1.8540083e+12 1.8539976e+12 21458 1 0 18
"01oct2018" "19:14:47" "01oct2018 19:14:47" 1.8540404e+12 1.8540297e+12 21458 1 0 18
"03oct2018" "19:20:27" "03oct2018 19:20:27" 1.8542136e+12  1.854203e+12 21460 3 0 18
"04oct2018" "19:27:01" "04oct2018 19:27:01" 1.8543004e+12 1.8542896e+12 21461 4 0 18
"04oct2018" "19:29:44" "04oct2018 19:29:44" 1.8543006e+12   1.85429e+12 21461 4 0 18
"05oct2018" "15:40:59" "05oct2018 15:40:59" 1.8543732e+12 1.8543625e+12 21462 5 0 18
"05oct2018" "20:33:45" "05oct2018 20:33:45" 1.8543908e+12   1.85438e+12 21462 5 0 18
"06oct2018" "08:40:49" "06oct2018 08:40:49" 1.8544344e+12 1.8544237e+12 21463 6 1 18
"06oct2018" "20:46:11" "06oct2018 20:46:11"  1.854478e+12 1.8544672e+12 21463 6 1 18
"08oct2018" "09:43:16" "08oct2018 09:43:16"  1.854611e+12 1.8546002e+12 21465 1 0 18
"08oct2018" "18:46:45" "08oct2018 18:46:45" 1.8546436e+12  1.854633e+12 21465 1 0 18
"08oct2018" "20:00:19" "08oct2018 20:00:19"  1.854648e+12 1.8546372e+12 21465 1 0 18
"09oct2018" "15:28:00" "09oct2018 15:28:00"  1.854718e+12 1.8547073e+12 21466 2 0 18
"01oct2018" "15:59:00" "01oct2018 15:59:00" 1.8540288e+12  1.854018e+12 21458 1 0 19
"01oct2018" "16:45:00" "01oct2018 16:45:00" 1.8540315e+12 1.8540208e+12 21458 1 0 19
"03oct2018" "06:40:30" "03oct2018 06:40:30"  1.854168e+12 1.8541572e+12 21460 3 0 19
"03oct2018" "19:49:35" "03oct2018 19:49:35" 1.8542154e+12 1.8542047e+12 21460 3 0 20
"12oct2018" "21:59:00" "12oct2018 21:59:00"  1.855001e+12   1.85499e+12 21469 5 0 20
"01oct2018" "17:04:42" "01oct2018 17:04:42" 1.8540327e+12  1.854022e+12 21458 1 0 21
"03oct2018" "18:59:31" "03oct2018 18:59:31" 1.8542124e+12 1.8542017e+12 21460 3 0 21
"06oct2018" "19:04:20" "06oct2018 19:04:20" 1.8544718e+12  1.854461e+12 21463 6 1 21
"09oct2018" "18:09:42" "09oct2018 18:09:42" 1.8547278e+12  1.854717e+12 21466 2 0 21
"01oct2018" "13:36:48" "01oct2018 13:36:48" 1.8540203e+12 1.8540095e+12 21458 1 0 22
end
format %tc timedate_ET
format %tc timedate_PT
format %td dayofweek
label values day week
label def week 0 "Sunday", modify
label def week 1 "Monday", modify
label def week 2 "Tuesday", modify
label def week 3 "Wednesday", modify
label def week 4 "Thursday", modify
label def week 5 "Friday", modify
label def week 6 "Saturday", modify
label values r_day rday
label def rday 0 "Weekday", modify
label def rday 1 "Weekend", modify

[William Lisowski]

Welcome to Statalist, Carlos. Thank you for presenting a good sample of your data using dataex - not every first-time poster manages this, and it's especially helpful with datetime data.

You are correct, your timedate variable includes both the date and the time within that date. You need to subtract off the timedate for the start of the day, which will leave you with just the time of day. Here is sample code that I think will start you in a useful direction.

generate double time_PT = timedate_PT - dhms(dofc(timedate_PT),0,0,0)
format time_PT %tcHH:MM:SS
generate aux = 0
// Identify cases between 9am and 6pm, in a weekend.
replace aux = 1 if time_PT > tc(9:00) & time_PT < tc(18:00) & r_day == 1
list timedate_PT time_PT day r_day aux in 1/5, noobs

=. list timedate_PT time_PT day r_day aux in 1/5, noobs

  +-----------------------------------------------------------+
  |        timedate_PT    time_PT         day     r_day   aux |
  |-----------------------------------------------------------|
  | 03oct2018 15:43:30   15:43:30   Wednesday   Weekday     0 |
  | 12oct2018 18:56:35   18:56:35      Friday   Weekday     0 |
  | 13oct2018 18:54:00   18:54:00    Saturday   Weekend     0 |
  | 14oct2018 10:26:48   10:26:48      Sunday   Weekend     1 |
  | 15oct2018 19:56:34   19:56:34      Monday   Weekday     0 |
  +-----------------------------------------------------------+

Let me add some bonus advice. Your use of dataex allows me to learn that you have stored your datetime variables as type float rather than type double. The output of help datetime tells us that datetime values should be stored as double, as I did in my code above. It is possible that by storing your datetime values as float you have slighly incorrect values stored. You should go back to program that read in the data and converted it from string to SIF values and change timedate_ET and timedate_PT to double.

[Carlos Macuada]

Thank you very much William. I will try this and let you know if I have any additional questions. And thanks also for catching that, I was aware of the double format, but reviewing my code, I forgot to add it on that var.

Thanks!
Carlos