Convert timeformat

[Helena Petersen]

Hi!

I'm having trouble converting the following timeformat, even though I have read the relevant help-documents, and tried many different appoaches.

I would like to generate new time variables from the "Time" variable, for example day and year.

I would also like to convert the original "Time" format to this format: 23/6/2021 (int) which I use in another dataset.

* Example generated by -dataex-. For more info, type help dataex
clear
input long ID str27 Time
7446409 "2021-06-23 13:18:00.0000000"
7448838 "2019-12-13 00:00:00.0000000"
7448838 "2019-12-13 00:00:00.0000000"
7448808 "2019-12-20 15:00:00.0000000"
7448808 "2019-12-21 07:00:00.0000000"
7448808 "2019-12-21 11:50:00.0000000"
7448808 "2019-12-21 12:20:00.0000000"
7448808 "2019-12-21 23:45:00.0000000"
7448808 "2019-12-22 06:20:00.0000000"
7448808 "2019-12-22 11:00:00.0000000"
7448808 "2019-12-22 11:03:00.0000000"
7448808 "2022-07-02 17:36:00.0000000"
7448808 "2022-07-02 17:37:00.0000000"
8325646 "2021-05-29 21:00:00.0000000"
   3429 "2020-08-23 23:44:00.0000000"
   3429 "2020-08-23 23:58:00.0000000"
   3429 "2020-08-26 14:27:00.0000000"
   3429 "2022-11-14 19:56:00.0000000"
   3429 "2022-11-15 06:00:00.0000000"
   3429 "2022-11-16 13:42:00.0000000"
   3429 "2022-11-18 12:00:00.0000000"
   3429 "2022-12-06 00:00:00.0000000"
   3429 "2023-05-03 06:39:00.0000000"
   3429 "2023-05-03 09:55:00.0000000"
 468920 "2018-06-03 00:00:00.0000000"
 468920 "2018-06-04 17:44:00.0000000"
 468920 "2018-06-04 17:44:00.0000000"
 468920 "2018-06-04 17:45:00.0000000"
 468920 "2018-06-04 17:45:00.0000000"
 468920 "2018-06-07 15:28:00.0000000"
 468920 "2018-06-07 17:19:00.0000000"
 468920 "2022-01-24 07:20:00.0000000"
 468920 "2022-08-05 08:00:00.0000000"
 468920 "2022-08-05 15:00:00.0000000"
 481627 "2020-08-05 08:00:00.0000000"
 481627 "2020-08-05 08:00:00.0000000"
 481627 "2021-03-04 08:56:00.0000000"
 481627 "2021-03-05 08:00:00.0000000"
 481627 "2022-10-11 13:44:00.0000000"
 481627 "2022-10-11 13:46:00.0000000"
 481627 "2022-10-11 13:46:00.0000000"
 482266 "2020-11-22 10:36:00.0000000"
 483973 "2019-01-06 04:37:00.0000000"
 484125 "2022-12-05 15:51:00.0000000"
7766168 "2019-10-07 00:00:00.0000000"
7766168 "2021-05-14 16:56:00.0000000"
7766168 "2021-06-18 08:50:00.0000000"
7766168 "2021-06-18 08:57:00.0000000"
7766168 "2021-06-29 13:45:00.0000000"
7766168 "2021-06-29 15:03:00.0000000"
7766168 "2021-11-10 14:54:00.0000000"
7766168 "2022-06-08 11:18:00.0000000"
7766168 "2022-06-26 13:37:00.0000000"
8158453 "2022-12-31 00:00:00.0000000"
8340291 "2023-02-19 09:53:00.0000000"
 485943 "2018-06-05 14:06:00.0000000"
 485943 "2018-06-05 14:07:00.0000000"
 485943 "2018-06-07 10:07:00.0000000"
 485943 "2018-06-07 10:07:00.0000000"
 485943 "2018-06-11 11:02:00.0000000"
 485943 "2018-06-11 11:03:00.0000000"
 485943 "2019-06-16 17:59:00.0000000"
 485943 "2019-06-16 19:39:00.0000000"
 485943 "2019-06-19 00:00:00.0000000"
 485943 "2020-01-06 11:14:00.0000000"
 485943 "2020-01-06 11:16:00.0000000"
 485943 "2020-01-08 14:01:00.0000000"
 485943 "2020-01-09 13:09:00.0000000"
 485943 "2020-01-13 06:28:00.0000000"
 485943 "2020-01-19 11:43:00.0000000"
 485943 "2020-01-21 13:20:00.0000000"
 485943 "2020-02-05 13:51:00.0000000"
 485943 "2020-02-06 20:10:00.0000000"
 485943 "2020-02-09 10:40:00.0000000"
   3825 "2020-09-19 00:00:00.0000000"
   3825 "2020-09-19 16:37:00.0000000"
   3825 "2020-09-20 16:43:00.0000000"
   3825 "2020-09-20 16:43:00.0000000"
   3825 "2020-09-20 18:26:00.0000000"
   3825 "2020-09-20 18:27:00.0000000"
   3825 "2020-09-22 00:00:00.0000000"
   3825 "2020-10-06 20:26:00.0000000"
   3825 "2020-10-09 13:49:00.0000000"
   3825 "2021-11-10 08:00:00.0000000"
   3825 "2021-11-10 08:00:00.0000000"
 499139 "2019-01-15 11:33:00.0000000"
 499139 "2019-02-07 13:19:00.0000000"
7273768 "2020-12-12 21:31:00.0000000"
7273768 "2020-12-12 21:32:00.0000000"
7273768 "2023-02-21 12:28:00.0000000"
   4357 "2020-11-13 00:00:00.0000000"
   4357 "2021-01-04 09:05:00.0000000"
 509773 "2023-03-05 00:00:00.0000000"
 509773 "2023-03-05 00:00:00.0000000"
 509773 "2023-03-05 01:09:00.0000000"
 509773 "2023-03-05 01:22:00.0000000"
 509773 "2023-03-05 02:30:00.0000000"
 509773 "2023-03-05 02:30:00.0000000"
 509773 "2023-03-09 10:51:00.0000000"
 509773 "2023-03-16 09:04:00.0000000"
end

Thank you!

[Nick Cox]

Thanks for the data example. You can make progress if you ask Stata to ignore the trailing zeros that aren't informative.

gen double datetime = clock(substr(Time, 1, 19), "YMD hms")
format datetime %tc 
gen dailydate = dofc(datetime)
format dailydate %td 
gen year = year(dailydate)

list in 1/10 

     +-------------------------------------------------------------------------------+
     |      ID                          Time             datetime   dailydate   year |
     |-------------------------------------------------------------------------------|
  1. | 7446409   2021-06-23 13:18:00.0000000   23jun2021 13:18:00   23jun2021   2021 |
  2. | 7448838   2019-12-13 00:00:00.0000000   13dec2019 00:00:00   13dec2019   2019 |
  3. | 7448838   2019-12-13 00:00:00.0000000   13dec2019 00:00:00   13dec2019   2019 |
  4. | 7448808   2019-12-20 15:00:00.0000000   20dec2019 15:00:00   20dec2019   2019 |
  5. | 7448808   2019-12-21 07:00:00.0000000   21dec2019 07:00:00   21dec2019   2019 |
     |-------------------------------------------------------------------------------|
  6. | 7448808   2019-12-21 11:50:00.0000000   21dec2019 11:50:00   21dec2019   2019 |
  7. | 7448808   2019-12-21 12:20:00.0000000   21dec2019 12:20:00   21dec2019   2019 |
  8. | 7448808   2019-12-21 23:45:00.0000000   21dec2019 23:45:00   21dec2019   2019 |
  9. | 7448808   2019-12-22 06:20:00.0000000   22dec2019 06:20:00   22dec2019   2019 |
 10. | 7448808   2019-12-22 11:00:00.0000000   22dec2019 11:00:00   22dec2019   2019 |
     +-------------------------------------------------------------------------------+

[Miguel Olivo-Villabrille]

This also works

gen t = Clock(Time, "YMDhms#")
format t %tC

You can then display variable t in whatever format you want by formatting t with the appropriately named command `format'.

[Nick Cox]

Note that clock() and Clock() are quite different functions.

[Helena Petersen]

Thank you so much, worked perfectly!