Extracting month value from timestamp

[Melat Kassa]

I am working with survey data and there is a variable called "timestamp". Here is an observation from that variable: 2021-10-15T15:02:39.624Z.

How can I generate two new variable, month and year, from this timestamp. I can do it with one observations but would like to put it in loop.

Here is what I did so far:

​​​​​​gen year_month = subinstr("2021-10-15T15:02:39.624Z", "-15T15:02:39.624Z", "",.)

[Maxime Bercholz]

Hi Melat,

Try this:

generate timestamp2 = subinstr(timestamp,"T"," ",.)
replace timestamp2 = subinstr(timestamp2,"Z","",.)
generate date = dofc(clock(timestamp2,"YMDhms"))
generate year = yofd(date)
generate month = mofd(date)
format year %ty
format month %tm

Notice that the first line of code replaces "T" by a blank space, to keep the date and time separated. Also notice that on the second line we're using timestamp2 as the argument of subinstr().

Maxime

[Ali Atia]

gen year = yofd(dofc(clock(timestamp,"YMD#hms#")))
gen month = month(dofc(clock(timestamp,"YMD#hms#")))

I assume you want the month number (e.g. October = 10) rather than the Stata Internal Format monthly date (e.g. October 2021 = 741). If you want the latter, use mofd( instead of month( in the second line of code.

[Maxime Bercholz]

There was a typo in my code: YMDhms needs to be enclosed in quotes, like in Ali's code. I did not think about the placeholder #, thanks Ali.

[Melat Kassa]

Thank you Ali and Maxime! Both options work but Ali's code gave me exactly what I wanted.

[Nick Cox]

I wrote a package named for its first command numdate on SSC. The package also includes convdate to convert from one kind of date-time to another and extrdate to extract components.

Here is a token example.

. clear

. set obs 1
Number of observations (_N) was 0, now 1.

. gen double time = clock("19 Nov 2021 17:57:00", "DMYhms")

. format time %tc

. list

     +--------------------+
     |               time |
     |--------------------|
  1. | 19nov2021 17:57:00 |
     +--------------------+

. convdate monthly mdate = time

. d

Contains data
 Observations:             1                  
    Variables:             2                  
------------------------------------------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
------------------------------------------------------------------------------------------------------------------
time            double  %tc                  
mdate           float   %tm                   monthly date from time
------------------------------------------------------------------------------------------------------------------
Sorted by:
     Note: Dataset has changed since last saved.

. l

     +------------------------------+
     |               time     mdate |
     |------------------------------|
  1. | 19nov2021 17:57:00   2021m11 |
     +------------------------------+