Round time in timestamp to nearest second, given precision to milliseconds

[Elc Estrera]

How would you round each timestamp below to the nearest second? I'm not talking about changing the timestamp's format or how it's displayed. I'd like to change the value so it's precise only to the nearest second rather than the millisecond.


1. | 2014Oct23 12:22:22.000 |
2. | 2014Oct23 12:22:22.001 |
3. | 2014Oct23 12:22:22.002 |
4. | 2014Oct23 12:22:22.003 |
5. | 2014Oct23 12:22:22.004 |
|------------------------|
6. | 2014Oct23 12:22:22.004 |
7. | 2014Oct23 12:22:22.006 |
8. | 2014Oct23 12:22:22.006 |
9. | 2014Oct23 12:22:22.008 |
10. | 2014Oct23 12:22:22.009 |
|------------------------|
11. | 2014Oct23 12:22:22.010 |
12. | 2014Oct23 12:22:22.011 |
13. | 2014Oct23 12:22:22.012 |
14. | 2014Oct23 12:22:22.012 |
15. | 2014Oct23 12:22:22.014 |
|------------------------|
16. | 2014Oct23 12:22:22.014 |
17. | 2014Oct23 12:22:22.016 |
18. | 2014Oct23 12:22:22.017 |
19. | 2014Oct23 12:22:22.017 |
20. | 2014Oct23 12:22:22.018

[Nick Cox]

As Stata's unit is milliseconds, rounding to the nearest thousand seems to be what you want. Note that dividing by 1000 (and then rounding to the nearest integer) would not help, as Stata won't then understand your date-time correctly. For example date-times around 7 April 2015 will just be date-times early in January 1960.

 
. di %23.0f  clock("7 April 2015 21:01:24.567", "DMY hms")
          1744059684567

. di %tc  clock("7 April 2015 21:01:24.567", "DMY hms")
07apr2015 21:01:24

. di %23.0f  round(clock("7 April 2015 21:01:24.567", "DMY hms"), 1000)
          1744059685000

. di %tc  round(clock("7 April 2015 21:01:24.567", "DMY hms"), 1000)
07apr2015 21:01:25

. di %23.0f  clock("7 April 2015 21:01:24.567", "DMY hms")/ 1000
             1744059685

. di %tc  clock("7 April 2015 21:01:24.567", "DMY hms")/ 1000
21jan1960 04:27:39

[Elc Estrera]

Thanks for the fast reply Nick. One follow-up question: How would I do this if my timestamp is already in Stata-readable format?

[Nick Cox]

Looks like the same question: round(varname, 1000) is the key.

[Elc Estrera]

Got it. Thanks Nick.

[Nick Cox]

If you want completed seconds, then use 1000 * floor(varname/1000).