rounded to the nearest minute

[River Huang]

Dear All, I have the following data set

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 ticker double time
"000937"  1813051799999.997
"300107" 1813051800000.0002
"002616" 1813051859999.9998
"600578" 1813051860000.0017
"000937"  1813051919999.998
"600340"      1813051920000
"600221" 1813051980000.0002
"600787" 1813051980000.0027
"000856"  1813052039999.999
"300446" 1813052039999.9998
end
format %tc time

and wish to round the time to the nearest minute. Any suggestions?

[Maarten Buis]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 ticker double time
"000937"  1813051799999.997
"300107" 1813051800000.0002
"002616" 1813051859999.9998
"600578" 1813051860000.0017
"000937"  1813051919999.998
"600340"      1813051920000
"600221" 1813051980000.0002
"600787" 1813051980000.0027
"000856"  1813052039999.999
"300446" 1813052039999.9998
end
format %tc time

// extract the elements of the date and time
gen month = month(dofc(time))
gen year = year(dofc(time))
gen day = day(dofc(time))
gen hour = hh(time)
gen min = mm(time)
gen sec = ss(time)

// round to whole minutes
replace min = min + 1 if sec > 30 & sec < .
replace sec = 0

// put the elements back together
gen double time_rm = mdyhms(month, day, year, hour, min, sec)
label var time_rm "time rounded to whole minutes"
format %tc time_rm

// clean up
drop month year day hour min sec

// admire the result
list

[Joseph Coveney]

A minute is sixty thousand milliseconds, and so round to the nearest sixty thousand.

.ÿversionÿ15.1

.ÿ
.ÿclearÿ*

.ÿ
.ÿinputÿstr9ÿtickerÿdoubleÿtime

ÿÿÿÿÿÿÿÿtickerÿÿÿÿÿÿÿÿtime
ÿÿ1.ÿ"000937"ÿÿ1813051799999.997
ÿÿ2.ÿ"300107"ÿ1813051800000.0002
ÿÿ3.ÿ"002616"ÿ1813051859999.9998
ÿÿ4.ÿ"600578"ÿ1813051860000.0017
ÿÿ5.ÿ"000937"ÿÿ1813051919999.998
ÿÿ6.ÿ"600340"ÿÿÿÿÿÿ1813051920000
ÿÿ7.ÿ"600221"ÿ1813051980000.0002
ÿÿ8.ÿ"600787"ÿ1813051980000.0027
ÿÿ9.ÿ"000856"ÿÿ1813052039999.999
ÿ10.ÿ"300446"ÿ1813052039999.9998
ÿ11.ÿend

.ÿ
.ÿgenerateÿdoubleÿround_dtÿ=ÿround(time,ÿ60000)

.ÿ
.ÿformatÿ%tcCCYY-NN-DD_HH:MM:SS.sssÿtimeÿround_dt

.ÿ
.ÿlist,ÿnoobsÿseparator(0)

ÿÿ+------------------------------------------------------------+
ÿÿ|ÿtickerÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿtimeÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿround_dtÿ|
ÿÿ|------------------------------------------------------------|
ÿÿ|ÿ000937ÿÿÿ2017-06-14ÿ09:29:59.999ÿÿÿ2017-06-14ÿ09:30:00.000ÿ|
ÿÿ|ÿ300107ÿÿÿ2017-06-14ÿ09:30:00.000ÿÿÿ2017-06-14ÿ09:30:00.000ÿ|
ÿÿ|ÿ002616ÿÿÿ2017-06-14ÿ09:30:59.999ÿÿÿ2017-06-14ÿ09:31:00.000ÿ|
ÿÿ|ÿ600578ÿÿÿ2017-06-14ÿ09:31:00.000ÿÿÿ2017-06-14ÿ09:31:00.000ÿ|
ÿÿ|ÿ000937ÿÿÿ2017-06-14ÿ09:31:59.999ÿÿÿ2017-06-14ÿ09:32:00.000ÿ|
ÿÿ|ÿ600340ÿÿÿ2017-06-14ÿ09:32:00.000ÿÿÿ2017-06-14ÿ09:32:00.000ÿ|
ÿÿ|ÿ600221ÿÿÿ2017-06-14ÿ09:33:00.000ÿÿÿ2017-06-14ÿ09:33:00.000ÿ|
ÿÿ|ÿ600787ÿÿÿ2017-06-14ÿ09:33:00.000ÿÿÿ2017-06-14ÿ09:33:00.000ÿ|
ÿÿ|ÿ000856ÿÿÿ2017-06-14ÿ09:33:59.999ÿÿÿ2017-06-14ÿ09:34:00.000ÿ|
ÿÿ|ÿ300446ÿÿÿ2017-06-14ÿ09:33:59.999ÿÿÿ2017-06-14ÿ09:34:00.000ÿ|
ÿÿ+------------------------------------------------------------+

.ÿ
.ÿexit

endÿofÿdo-file

[River Huang]

Many thanks, Maarten. It works.

[River Huang]

Many thanks, Joseph. It works quite well.