Changing Odd Date Strings to Manageable Dates

[Jared Greathouse]

I have some data from Google trends that I'd like to maybe work with, following this interesting story. Consider an excerpt

* Example generated by -dataex-. For more info, type help dataex
clear
input str24 v1 str47 searchrate str20 state float id
"2023-04-28T18" "15"  "Alabama" 1
"2023-04-28T19" "12"  "Alabama" 1
"2023-04-28T20" "8"   "Alabama" 1
"2023-04-28T21" "15"  "Alabama" 1
"2023-04-28T22" "10"  "Alabama" 1
"2023-04-28T23" "21"  "Alabama" 1
"2023-04-29T00" "50"  "Alabama" 1
"2023-04-29T01" "18"  "Alabama" 1
"2023-04-29T02" "28"  "Alabama" 1
"2023-04-29T03" "0"   "Alabama" 1
"2023-04-29T04" "30"  "Alabama" 1
"2023-04-29T05" "72"  "Alabama" 1
"2023-04-29T06" "37"  "Alabama" 1
"2023-04-29T07" "21"  "Alabama" 1
"2023-04-29T08" "6"   "Alabama" 1
"2023-04-29T09" "18"  "Alabama" 1
"2023-04-29T10" "12"  "Alabama" 1
"2023-04-29T11" "16"  "Alabama" 1
"2023-04-29T12" "6"   "Alabama" 1
"2023-04-29T13" "14"  "Alabama" 1
"2023-04-29T14" "11"  "Alabama" 1
"2023-04-29T15" "16"  "Alabama" 1
"2023-04-29T16" "21"  "Alabama" 1
"2023-04-29T17" "23"  "Alabama" 1
"2023-04-29T18" "13"  "Alabama" 1
"2023-04-29T19" "10"  "Alabama" 1
"2023-04-29T20" "10"  "Alabama" 1
"2023-04-29T21" "13"  "Alabama" 1
"2023-04-29T22" "13"  "Alabama" 1
"2023-04-29T23" "16"  "Alabama" 1
"2023-04-30T00" "18"  "Alabama" 1
"2023-04-30T01" "23"  "Alabama" 1
"2023-04-30T02" "27"  "Alabama" 1
"2023-04-30T03" "34"  "Alabama" 1
"2023-04-30T04" "35"  "Alabama" 1
"2023-04-30T05" "0"   "Alabama" 1
"2023-04-30T06" "0"   "Alabama" 1
"2023-04-30T07" "26"  "Alabama" 1
"2023-04-30T08" "19"  "Alabama" 1
"2023-04-30T09" "18"  "Alabama" 1
"2023-04-30T10" "6"   "Alabama" 1
"2023-04-30T11" "23"  "Alabama" 1
"2023-04-30T12" "19"  "Alabama" 1
"2023-04-30T13" "11"  "Alabama" 1
"2023-04-30T14" "11"  "Alabama" 1
"2023-04-30T15" "14"  "Alabama" 1
"2023-04-30T16" "0"   "Alabama" 1
"2023-04-30T17" "12"  "Alabama" 1
"2023-04-30T18" "9"   "Alabama" 1
"2023-04-30T19" "13"  "Alabama" 1
"2023-04-30T20" "16"  "Alabama" 1
"2023-04-30T21" "21"  "Alabama" 1
"2023-04-30T22" "20"  "Alabama" 1
"2023-04-30T23" "18"  "Alabama" 1
"2023-05-01T00" "20"  "Alabama" 1
"2023-05-01T01" "27"  "Alabama" 1
"2023-05-01T02" "20"  "Alabama" 1
"2023-05-01T03" "0"   "Alabama" 1
"2023-05-01T04" "62"  "Alabama" 1
"2023-05-01T05" "0"   "Alabama" 1
"2023-05-01T06" "0"   "Alabama" 1
"2023-05-01T07" "11"  "Alabama" 1
"2023-05-01T08" "31"  "Alabama" 1
"2023-05-01T09" "28"  "Alabama" 1
"2023-05-01T10" "17"  "Alabama" 1
"2023-05-01T11" "21"  "Alabama" 1
"2023-05-01T12" "17"  "Alabama" 1
"2023-05-01T13" "22"  "Alabama" 1
"2023-05-01T14" "17"  "Alabama" 1
"2023-05-01T15" "15"  "Alabama" 1
"2023-05-01T16" "11"  "Alabama" 1
"2023-05-01T17" "15"  "Alabama" 1
"2023-05-01T18" "17"  "Alabama" 1
"2023-05-01T19" "21"  "Alabama" 1
"2023-05-01T20" "12"  "Alabama" 1
"2023-05-01T21" "9"   "Alabama" 1
"2023-05-01T22" "13"  "Alabama" 1
"2023-05-01T23" "13"  "Alabama" 1
"2023-05-02T00" "16"  "Alabama" 1
"2023-05-02T01" "45"  "Alabama" 1
"2023-05-02T02" "28"  "Alabama" 1
"2023-05-02T03" "22"  "Alabama" 1
"2023-05-02T04" "18"  "Alabama" 1
"2023-05-02T05" "100" "Alabama" 1
"2023-05-02T06" "22"  "Alabama" 1
"2023-05-02T07" "22"  "Alabama" 1
"2023-05-02T08" "25"  "Alabama" 1
"2023-05-02T09" "18"  "Alabama" 1
"2023-05-02T10" "34"  "Alabama" 1
"2023-05-02T11" "17"  "Alabama" 1
"2023-05-02T12" "18"  "Alabama" 1
"2023-05-02T13" "20"  "Alabama" 1
"2023-05-02T14" "17"  "Alabama" 1
"2023-05-02T15" "9"   "Alabama" 1
"2023-05-02T16" "14"  "Alabama" 1
"2023-05-02T17" "14"  "Alabama" 1
"2023-05-02T18" "17"  "Alabama" 1
"2023-05-02T19" "15"  "Alabama" 1
"2023-05-02T20" "14"  "Alabama" 1
"2023-05-02T21" "12"  "Alabama" 1
end

generate double time = clock(v1, "20YMDh")

I wanna generate a time variable, with something like 01may202312pm. for all the states of interest, but at the moment Stata generates them as missing. How might I do this, I've never seen this kind of date before?

[Leonardo Guizzetti]

This is time formatted in ISO 8601 format. It allows for partial date/time values, and is written using only numerals, and from most to least coarse unit of time from left to right. Time, if present, is always separated by the letter "T".

You can use the clock() function with an appropriate mask, but you need to first substitute the letter "T" with something clock() will ignore.

gen double want = clock(subinstr(v1, "T", ":", 1), "YMD h")
format %tc want

[Jared Greathouse]

Okay this makes sense. One more thing, though.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte searchrate float grow double time
  4           .  1.998324e+12
  5         .25 1.9983276e+12
  5           0 1.9983312e+12
  5           0 1.9983348e+12
  4         -.2 1.9983384e+12
  5         .25  1.998342e+12
  4         -.2 1.9983456e+12
  8           1 1.9983492e+12
  0          -1 1.9983528e+12
  0           . 1.9983564e+12
  0           .   1.99836e+12
 18           . 1.9983636e+12
 25    .3888889 1.9983672e+12
  0          -1 1.9983708e+12
  0           . 1.9983744e+12
  3           .  1.998378e+12
  3           0 1.9983816e+12
  4    .3333333 1.9983852e+12
  4           0 1.9983888e+12
  4           0 1.9983924e+12
  3        -.25  1.998396e+12
  7   1.3333334 1.9983996e+12
  4   -.4285714 1.9984032e+12
  6          .5 1.9984068e+12
  3         -.5 1.9984104e+12
  4    .3333333  1.998414e+12
  8           1 1.9984176e+12
  5       -.375 1.9984212e+12
  2         -.6 1.9984248e+12
  5         1.5 1.9984284e+12
  7          .4  1.998432e+12
 10    .4285714 1.9984356e+12
  0          -1 1.9984392e+12
  8           . 1.9984428e+12
  0          -1 1.9984464e+12
 34           .   1.99845e+12
  0          -1 1.9984536e+12
 25           . 1.9984572e+12
  0          -1 1.9984608e+12
 10           . 1.9984644e+12
  4         -.6  1.998468e+12
  7         .75 1.9984716e+12
  6  -.14285715 1.9984752e+12
  5  -.16666667 1.9984788e+12
  7          .4 1.9984824e+12
  4   -.4285714  1.998486e+12
  6          .5 1.9984896e+12
  3         -.5 1.9984932e+12
  6           1 1.9984968e+12
  3         -.5 1.9985004e+12
  7   1.3333334  1.998504e+12
  7           0 1.9985076e+12
  4   -.4285714 1.9985112e+12
  9        1.25 1.9985148e+12
  5  -.44444445 1.9985184e+12
 13         1.6  1.998522e+12
  8   -.3846154 1.9985256e+12
 16           1 1.9985292e+12
  0          -1 1.9985328e+12
  0           . 1.9985364e+12
 22           .   1.99854e+12
 11         -.5 1.9985436e+12
  7   -.3636364 1.9985472e+12
  6  -.14285715 1.9985508e+12
  8    .3333333 1.9985544e+12
 11        .375  1.998558e+12
 13    .1818182 1.9985616e+12
 13           0 1.9985652e+12
 16   .23076923 1.9985688e+12
 12        -.25 1.9985724e+12
 19    .5833333  1.998576e+12
 26   .36842105 1.9985796e+12
 20  -.23076923 1.9985832e+12
 25         .25 1.9985868e+12
 26         .04 1.9985904e+12
 36    .3846154  1.998594e+12
 29  -.19444445 1.9985976e+12
 37   .27586207 1.9986012e+12
 45    .2162162 1.9986048e+12
 52   .15555556 1.9986084e+12
 60   .15384616  1.998612e+12
100    .6666667 1.9986156e+12
 58        -.42 1.9986192e+12
 30   -.4827586 1.9986228e+12
 41    .3666667 1.9986264e+12
 41           0   1.99863e+12
 34   -.1707317 1.9986336e+12
 30  -.11764706 1.9986372e+12
 26  -.13333334 1.9986408e+12
 20  -.23076923 1.9986444e+12
 24          .2  1.998648e+12
 23  -.04166667 1.9986516e+12
 18   -.2173913 1.9986552e+12
 23    .2777778 1.9986588e+12
 22  -.04347826 1.9986624e+12
 24    .0909091  1.998666e+12
 31   .29166666 1.9986696e+12
 24  -.22580644 1.9986732e+12
 29   .20833333 1.9986768e+12
 31   .06896552 1.9986804e+12
 31           0  1.998684e+12
 34    .0967742 1.9986876e+12
 38   .11764706 1.9986912e+12
 52   .36842105 1.9986948e+12
 53   .01923077 1.9986984e+12
 64   .20754717  1.998702e+12
 69     .078125 1.9987056e+12
 68 -.014492754 1.9987092e+12
 71   .04411765 1.9987128e+12
 44   -.3802817 1.9987164e+12
 40   -.0909091   1.99872e+12
 24         -.4 1.9987236e+12
 26   .08333334 1.9987272e+12
 23  -.11538462 1.9987308e+12
 20  -.13043478 1.9987344e+12
 19        -.05  1.998738e+12
 17  -.10526316 1.9987416e+12
 15  -.11764706 1.9987452e+12
 17   .13333334 1.9987488e+12
 23    .3529412 1.9987524e+12
 21  -.08695652  1.998756e+12
 25    .1904762 1.9987596e+12
 20         -.2 1.9987632e+12
 23         .15 1.9987668e+12
 25   .08695652 1.9987704e+12
 33         .32  1.998774e+12
 36    .0909091 1.9987776e+12
 35  -.02777778 1.9987812e+12
 43   .22857143 1.9987848e+12
 65    .5116279 1.9987884e+12
 49  -.24615385  1.998792e+12
 45  -.08163265 1.9987956e+12
 51   .13333334 1.9987992e+12
 48  -.05882353 1.9988028e+12
 28   -.4166667 1.9988064e+12
 24  -.14285715   1.99881e+12
 21       -.125 1.9988136e+12
 15   -.2857143 1.9988172e+12
 16   .06666667 1.9988208e+12
 19       .1875 1.9988244e+12
 15   -.2105263  1.998828e+12
 14  -.06666667 1.9988316e+12
 19    .3571429 1.9988352e+12
 14   -.2631579 1.9988388e+12
 19    .3571429 1.9988424e+12
 23    .2105263  1.998846e+12
 19  -.17391305 1.9988496e+12
 18  -.05263158 1.9988532e+12
 17  -.05555556 1.9988568e+12
 16  -.05882353 1.9988604e+12
 25       .5625  1.998864e+12
 29         .16 1.9988676e+12
 35   .20689656 1.9988712e+12
 62    .7714286 1.9988748e+12
 96    .5483871 1.9988784e+12
 30      -.6875  1.998882e+12
 42          .4 1.9988856e+12
 30   -.2857143 1.9988892e+12
 21         -.3 1.9988928e+12
 11   -.4761905 1.9988964e+12
 12    .0909091    1.9989e+12
 16    .3333333 1.9989036e+12
 16           0 1.9989072e+12
 13      -.1875 1.9989108e+12
 10  -.23076923 1.9989144e+12
 19          .9  1.998918e+12
 14   -.2631579 1.9989216e+12
 11   -.2142857 1.9989252e+12
end

tsset time, c 

loc int_time: di `=tc(01may2023 00:00:00)'



line search time, xli(`int_time')

When I graph the time series, I get the intervention line at May 6th... but why, the local macro is defined by the scalar value of may 1st.... right? Shouldn't the reference line be at the first of the month?

[Leonardo Guizzetti]

Hmm...I'm not sure what's going on here. I would tend to agree the line should be at May 1st.

[Nick Cox]

This works with the data example in #1. Note the # syntax.

generate double time = clock(v1, "20YMD#h")

tsset time, c delta(`= 60 * 60 * 1000')

loc int_time =tc(01may2023 00:00:00)

destring search, replace

line search time, xli(`int_time')

[Andrew Musau]

Time values are very large numbers, so it becomes a precision issue. You need:

loc int_time: di %17.0f `=tc(01may2023 00:00:00)'
line search time, xli(`int_time') xlab(`int_time')  xtitle("")

[Jared Greathouse]

precision

Ahhh yes, my dear old friend precision, how I've missed you.