Converting date timestamp

[robert seru]

Greetings,
I kindly requesting for assistance in converting date-timestamp e.g."Feb 19, 2017 9:01:36 PM" to "19feb2017"
Thanks

[River Huang]

Please try:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str30 time
"Feb 19, 2017 9:01:36 PM"
end

gen double eventtime = clock(time, "MDYhms")
format eventtime %tc
gen date = dofc(eventtime)
format date %td

[Nick Cox]

Did you study

help datetime

because that contains all the information you need. I often use display to check out suggested syntax. For example,

. di %tc  clock("Feb 19, 2017 9:01:36 PM", "MDY hms")
19feb2017 21:01:36

. di %td  dofc(clock("Feb 19, 2017 9:01:36 PM", "MDY hms"))
19feb2017

For a new variable, this should be safe:

gen double datetime = clock(whatever, "MDY hms") 
format %tc datetime 
gen date = dofc(datetime) 
format %td date

[robert seru]

Thank all you very much.
Very helpful.
Thnx Nick Cox, I've been reading through the "help datetime"
Best

[robert seru]

Greetings.
I have this date format ddmmmyyy hh:mm:ss and I would want to generate a variable with only the time in 24hr format

subdate1
--------------------------
29oct2018 08:01:06
09oct2018 13:54:06
09oct2018 13:54:06

e.g if date is 29oct2018 08:01:06 I would want a variable with value as 08:01:06

Thanks

[Nick Cox]

Experienced users here do just what you are expected to do, read the help for datetime, and find suitable functions.

There are probably better ways to do it, but this one works:

. scalar dt = clock("29oct2018 08:01:06", "DMY hms")

. di %tcHH:MM:SS (dt - cofd(dofc(dt)))
08:01:06

In your case, you don't provide a decent data example (please do read and act on FAQ Advice #12) but

generate double wanted = subdate1 - cofd(dofc(subdate1)) 
format wanted %tcHH:MM:SS

is an indication.

[robert seru]

Thanks, Nick
Is it possible to categorize ranges of time?
(08:30:00 - 20:30:00) DayShift
(20:31:00 - 08:20:00) NightShift Thanks Robert

[Joseph Coveney]

Is it possible to categorize ranges of time?

You mean like this? (Continuing with Nick's example)

.ÿversionÿ15.1

.ÿ
.ÿclearÿ*

.ÿ
.ÿquietlyÿsetÿobsÿ2

.ÿ
.ÿgenerateÿdoubleÿcalendar_dtÿ=ÿclock("29oct2018ÿ08:01:06",ÿ"DMYÿhms")

.ÿquietlyÿreplaceÿcalendar_dtÿ=ÿclock("2018-10-29ÿ21:00:01",ÿ"YMDhms")ÿinÿ2

.ÿformatÿcalendar_dtÿ%tcCCYY-NN-DD_HH:MM:SS

.ÿ
.ÿgenerateÿdoubleÿtodÿ=ÿcalendar_dtÿ-ÿcofd(dofc(calendar_dt))

.ÿformatÿtodÿ%tcHH:MM:SS

.ÿ
.ÿgenerateÿbyteÿshiftÿ=ÿtodÿ>ÿclock("20:30:00",ÿ"hms")

.ÿlabelÿdefineÿShiftsÿ0ÿDayÿ1ÿNight

.ÿlabelÿvaluesÿshiftÿShifts

.ÿ
.ÿlist,ÿnoobs

ÿÿ+----------------------------------------+
ÿÿ|ÿÿÿÿÿÿÿÿÿcalendar_dtÿÿÿÿÿÿÿÿtodÿÿÿshiftÿ|
ÿÿ|----------------------------------------|
ÿÿ|ÿ2018-10-29ÿ08:01:06ÿÿÿ08:01:06ÿÿÿÿÿDayÿ|
ÿÿ|ÿ2018-10-29ÿ21:00:01ÿÿÿ21:00:01ÿÿÿNightÿ|
ÿÿ+----------------------------------------+

.ÿ
.ÿexit

endÿofÿdo-file


.

This will go until midnight, but you can use the inrange() function instead to carve out the time intervals that you want.

[robert seru]

Thanks Joseph

[Bernie Seville]

Hi all, Just a follow up question on this thread, I have a timestamp (start time) 2019-10-25T16:26:40.847+08:00 and below is the process I did.
generate start_time= substr(starttime ,1,16)
replace start_time= subinstr(start_time, "T", " ",.)
gen double s_time = clock(start_time, "YMDhm")
format %tc s_time

Is there a shorter way to do this?
Thanks in advance
Bernie

[Ruth-Alma Turkson-Ocran]

Hi all,

I would like to build on/add to Robert's question about creating time ranges and categorizing them as daytime or nighttime. I am trying to categorize all times between 0700 and 2300 as "daytime" and all times from 2301 to 0659 as "nighttime." I tried using the following code:

gen daytime=(time2>=clock("07:00", "hm") & time2<clock("23:00", "hm"))
label define daytime 1 "1: daytime" 0 "0: nighttime", replace
label variable daytime "Time of Day"
label values daytime daytime
tab daytime

However, it put all my time into nighttime, but I do know I should have both categories. I am not sure how to fix this and suggestions will be helpful. Thank you.

Here is a sample of the data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(time time2) float(day month year)
     -1893417420000      -1893417420000 5 12 2019
     -1.8934173e+12      -1.8934173e+12 5 12 2019
     -1893417240000      -1893417240000 5 12 2019
     -1893415860000      -1893415860000 5 12 2019
     -1893415680000      -1893415680000 5 12 2019
     -1893414060000      -1893414060000 5 12 2019
-1893412260000.0002 -1893412260000.0002 5 12 2019
     -1893410460000      -1893410460000 5 12 2019
     -1893408660000      -1893408660000 5 12 2019
-1893406860000.0002 -1893406860000.0002 5 12 2019
     -1893405060000      -1893405060000 5 12 2019
     -1893403260000      -1893403260000 5 12 2019
     -1893403080000      -1893403080000 5 12 2019
-1893401460000.0002 -1893401460000.0002 5 12 2019
-1893401279999.9998 -1893401279999.9998 5 12 2019
     -1893399660000      -1893399660000 5 12 2019
     -1893399420000      -1893399420000 5 12 2019
     -1893397860000      -1893397860000 5 12 2019
     -1893397680000      -1893397680000 5 12 2019
-1893396060000.0002 -1893396060000.0002 5 12 2019
     -1893394260000      -1893394260000 5 12 2019
     -1893392460000      -1893392460000 5 12 2019
-1893390660000.0002 -1893390660000.0002 5 12 2019
     -1893388860000      -1893388860000 5 12 2019
     -1893387060000      -1893387060000 5 12 2019
-1893385260000.0002 -1893385260000.0002 5 12 2019
-1893385079999.9998 -1893385079999.9998 5 12 2019
     -1893383460000      -1893383460000 5 12 2019
-1893383340000.0002 -1893383340000.0002 5 12 2019
     -1893381660000      -1893381660000 5 12 2019
     -1893381480000      -1893381480000 5 12 2019
-1893379860000.0002 -1893379860000.0002 5 12 2019
     -1893378060000      -1893378060000 5 12 2019
     -1893377880000      -1893377880000 5 12 2019
     -1893376260000      -1893376260000 5 12 2019
-1893374460000.0002 -1893374460000.0002 5 12 2019
     -1893372660000      -1893372660000 5 12 2019
     -1893372480000      -1893372480000 5 12 2019
     -1893370860000      -1893370860000 5 12 2019
-1893455460000.0002 -1893455460000.0002 5 12 2019
end
format %tcHH:MM:SS time
format %tc_HH:MM time2
label var time "Time"
label var time2 "Time"
label var month "Month"
label var year "Year"

[Clyde Schechter]

OK. I don't understand why you have two variables, time, and time2, that always have the same values. But I'll just ignore that.

The problem is that by formatting those variables %tcHH:MM:SS and %tc_HH:MM you have deceived yourself into thinking that they only have the time of day in them, and not the date. But that is never true of Stata clock variables: they always contain a date. And the date you have is December 31, 1899. (Apply the format %tc to those variables and you will see this for yourself.) But that is the wrong reference date for Stata, so comparing it to clock(any time) will never give you what you expect. (This reference date commonly arises when you import data from Excel, because that is the reference date that most versions of Excel use.) So you need to change that variable to have Stata's reference date, which is January 1, 1960. The simplest way to do it, though, is to just subtract out whatever date is in there. This code gives you what you want:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(time time2) float(day month year)
     -1893417420000      -1893417420000 5 12 2019
     -1.8934173e+12      -1.8934173e+12 5 12 2019
     -1893417240000      -1893417240000 5 12 2019
     -1893415860000      -1893415860000 5 12 2019
     -1893415680000      -1893415680000 5 12 2019
     -1893414060000      -1893414060000 5 12 2019
-1893412260000.0002 -1893412260000.0002 5 12 2019
     -1893410460000      -1893410460000 5 12 2019
     -1893408660000      -1893408660000 5 12 2019
-1893406860000.0002 -1893406860000.0002 5 12 2019
     -1893405060000      -1893405060000 5 12 2019
     -1893403260000      -1893403260000 5 12 2019
     -1893403080000      -1893403080000 5 12 2019
-1893401460000.0002 -1893401460000.0002 5 12 2019
-1893401279999.9998 -1893401279999.9998 5 12 2019
     -1893399660000      -1893399660000 5 12 2019
     -1893399420000      -1893399420000 5 12 2019
     -1893397860000      -1893397860000 5 12 2019
     -1893397680000      -1893397680000 5 12 2019
-1893396060000.0002 -1893396060000.0002 5 12 2019
     -1893394260000      -1893394260000 5 12 2019
     -1893392460000      -1893392460000 5 12 2019
-1893390660000.0002 -1893390660000.0002 5 12 2019
     -1893388860000      -1893388860000 5 12 2019
     -1893387060000      -1893387060000 5 12 2019
-1893385260000.0002 -1893385260000.0002 5 12 2019
-1893385079999.9998 -1893385079999.9998 5 12 2019
     -1893383460000      -1893383460000 5 12 2019
-1893383340000.0002 -1893383340000.0002 5 12 2019
     -1893381660000      -1893381660000 5 12 2019
     -1893381480000      -1893381480000 5 12 2019
-1893379860000.0002 -1893379860000.0002 5 12 2019
     -1893378060000      -1893378060000 5 12 2019
     -1893377880000      -1893377880000 5 12 2019
     -1893376260000      -1893376260000 5 12 2019
-1893374460000.0002 -1893374460000.0002 5 12 2019
     -1893372660000      -1893372660000 5 12 2019
     -1893372480000      -1893372480000 5 12 2019
     -1893370860000      -1893370860000 5 12 2019
-1893455460000.0002 -1893455460000.0002 5 12 2019
end
format %tcHH:MM:SS time
format %tc_HH:MM time2
label var time "Time"
label var time2 "Time"
label var month "Month"
label var year "Year"

gen double pure_time2 = time2-cofd(dofc(time2))
format pure_time2 %tcHH:MM
gen byte daytime = inrange(pure_time2, clock("07:00", "hm"), clock("23:00", "hm"))

[Ruth-Alma Turkson-Ocran]

Thank you so much; sorry about including both time and time2, I should have included just time, time2 was created from appending dataset and should be ignored.

On the second issue: Yes, you are correct, the data was imported from Excel and this caused the issue with the date format.

Thank you so much this code worked!