Keep only the date and the hour in a datetime variable

[Ioannis pragidis]

Hi everyone,

I work with hourly data and in the example below I transform the string variable hour in the %tc formatted variable date, using the following code:

gen double date=clock(hour, "YMD h")
format date %tc

However, after I declare the dataset as a panel data I get the following:

Panel variable: clientid (unbalanced)
Time variable: date, 07dec2020 00:00:00 to 25jun2023 23:00:00, but with gaps
Delta: .001 seconds

However, I would expect Delta to be on an hourly basis rather than seconds. Could you please anyone help me to get this right?

* Example generated by -dataex-. For more info, type help dataex
clear
input long clientid double date str13 hour
  6 1.9229184e+12 "2020-12-07 0"
  6 2.0033496e+12 "2023-06-25 22"
  6 2.0033532e+12 "2023-06-25 23"
  7 1.9229184e+12 "2020-12-07 0"
  7 2.0033496e+12 "2023-06-25 22"
  7 2.0033532e+12 "2023-06-25 23"
  9 1.9229184e+12 "2020-12-07 0"
 10 1.9229184e+12 "2020-12-07 0"
 10 2.0033496e+12 "2023-06-25 22"
 10 2.0033532e+12 "2023-06-25 23"
 17 1.9229184e+12 "2020-12-07 0"
 17 2.0033532e+12 "2023-06-25 23"
 30 1.9229184e+12 "2020-12-07 0"
 30 2.0033532e+12 "2023-06-25 23"
 32 1.9229184e+12 "2020-12-07 0"
 32 2.0033496e+12 "2023-06-25 22"
 32 2.0033532e+12 "2023-06-25 23"
 41 1.9229184e+12 "2020-12-07 0"
 41  1.922922e+12 "2020-12-07 1"
 41 2.0033532e+12 "2023-06-25 23"
 55 1.9229184e+12 "2020-12-07 0"
 55  1.922922e+12 "2020-12-07 1"
 55 2.0033496e+12 "2023-06-25 22"
 55 2.0033532e+12 "2023-06-25 23"
 59 1.9229184e+12 "2020-12-07 0"
 59 2.0033496e+12 "2023-06-25 22"
 59 2.0033532e+12 "2023-06-25 23"
 61 1.9229184e+12 "2020-12-07 0"
 61  1.922922e+12 "2020-12-07 1"
 61 2.0033496e+12 "2023-06-25 22"
 61 2.0033532e+12 "2023-06-25 23"
 67 1.9229184e+12 "2020-12-07 0"
 67 2.0033496e+12 "2023-06-25 22"
 67 2.0033532e+12 "2023-06-25 23"
 75 1.9229184e+12 "2020-12-07 0"
 75 2.0033496e+12 "2023-06-25 22"
 75 2.0033532e+12 "2023-06-25 23"
 83 1.9229184e+12 "2020-12-07 0"
 83 2.0033496e+12 "2023-06-25 22"
 83 2.0033532e+12 "2023-06-25 23"
110 1.9229184e+12 "2020-12-07 0"
110  1.922922e+12 "2020-12-07 1"
125 1.9229184e+12 "2020-12-07 0"
125 2.0033496e+12 "2023-06-25 22"
125 2.0033532e+12 "2023-06-25 23"
131 1.9229184e+12 "2020-12-07 0"
131  1.922922e+12 "2020-12-07 1"
131 2.0033496e+12 "2023-06-25 22"
131 2.0033532e+12 "2023-06-25 23"
136 1.9229184e+12 "2020-12-07 0"
136 2.0033496e+12 "2023-06-25 22"
136 2.0033532e+12 "2023-06-25 23"
141 1.9229184e+12 "2020-12-07 0"
141 2.0033496e+12 "2023-06-25 22"
141 2.0033532e+12 "2023-06-25 23"
160 1.9229184e+12 "2020-12-07 0"
160 2.0033496e+12 "2023-06-25 22"
160 2.0033532e+12 "2023-06-25 23"
173 1.9229184e+12 "2020-12-07 0"
173 2.0033532e+12 "2023-06-25 23"
174 1.9229184e+12 "2020-12-07 0"
174 2.0033496e+12 "2023-06-25 22"
174 2.0033532e+12 "2023-06-25 23"
180 1.9229184e+12 "2020-12-07 0"
180  1.922922e+12 "2020-12-07 1"
180 2.0033496e+12 "2023-06-25 22"
180 2.0033532e+12 "2023-06-25 23"
184 1.9229184e+12 "2020-12-07 0"
184 2.0033496e+12 "2023-06-25 22"
184 2.0033532e+12 "2023-06-25 23"
197 1.9229184e+12 "2020-12-07 0"
212 1.9229184e+12 "2020-12-07 0"
212 2.0033496e+12 "2023-06-25 22"
212 2.0033532e+12 "2023-06-25 23"
218 1.9229184e+12 "2020-12-07 0"
218  1.922922e+12 "2020-12-07 1"
218 2.0033496e+12 "2023-06-25 22"
218 2.0033532e+12 "2023-06-25 23"
224 1.9229184e+12 "2020-12-07 0"
224 2.0033496e+12 "2023-06-25 22"
224 2.0033532e+12 "2023-06-25 23"
237 1.9229184e+12 "2020-12-07 0"
237 2.0033496e+12 "2023-06-25 22"
237 2.0033532e+12 "2023-06-25 23"
256 1.9229184e+12 "2020-12-07 0"
256 2.0033496e+12 "2023-06-25 22"
256 2.0033532e+12 "2023-06-25 23"
264 1.9229184e+12 "2020-12-07 0"
265 1.9229184e+12 "2020-12-07 0"
265  1.922922e+12 "2020-12-07 1"
265 2.0033532e+12 "2023-06-25 23"
270 1.9229184e+12 "2020-12-07 0"
270 2.0033496e+12 "2023-06-25 22"
270 2.0033532e+12 "2023-06-25 23"
300 1.9229184e+12 "2020-12-07 0"
300 2.0033532e+12 "2023-06-25 23"
304 1.9229184e+12 "2020-12-07 0"
304 2.0033496e+12 "2023-06-25 22"
304 2.0033532e+12 "2023-06-25 23"
305 1.9229184e+12 "2020-12-07 0"
end
format %tc date

Best,

Ioannis

[Nick Cox]

There is no specific support in Stata for date-times that are every hour. or every minute, or every second, or evey 0.1 second, and so on. And listing some of the datetime possibilities that aren't specifically supported underlines that there could be many of them.

Those mentioned are all special cases of date-time data for which the units are milliseconds; it's just that they will be in multiples of 60 * 60000, or 60000, or 1000, 100, or whatever.

You can and for most purposes should declare a display format that suppresses useless display of (minutes and) seconds which you don't care about. For example,

. di %tcNN_Mon_CCYY_HH 2.0033532e+12
06 Jun 2023 23

See

help datetime display formats

Also, if you're using tsset or xtset, specify delta(3600000)

[Ioannis pragidis]

Thank you very much!