Figuring out if a case is within a range

[Bridget Diamond-Welch]

Hi all:

I have data that indicates the time people were booked (BookingDateTime) into and released (ReleaseDateTime) from jail. Both variables are double %tc.

I need to create a variable that indicates if they were present in a given quarter. People can be in multiple quarters (e.g. if they were in jail for 2 years). I'd want to know that. My idea would be to create variables called q1, q2, q3 -- and then give each person a 1 or a 0 based on whether they were booked in during that quarter at all or not. My quarters are a little wonky, given our reporting. But if you just figure Jan 1 -March 31, I can adjust.

For example, someone could be booked in on 1oct2014 12:31:42 and released on 15jan2019 01:11:15
This person should get a 1 for all quarters between those two dates.

My initial thought was to

gen quarter = .
replace quarter = 0 if BookingDateTime <=tc(31jan2018 23:59:59)
replace quarter = 1 if BookingDateTime >=tc(01feb2018 00:00:00) & BookingDateTime <= tc(31apr2018 23:59:59)
replace quarter = 2 if BookingDateTime >=tc(01may2018 00:00:00) & BookingDateTime <= tc(31jul2018 23:59:59)
...

The issue here is that this makes the quarters mutually exclusive. Someone is either in a quarter or not based on BookingDateTime. While this would accurately place them within the first quarter they were in jail, it would not indicate if they were in jail subsequent quarters.

I then thought to do the similar above but also use | ReleaseDateTime. But that caused similar issues.

What I need to know is if their time range overlaps with a quarter.

I am trying to get this to work: https://www.stata.com/statalist/arch.../msg01004.html given my different time format, and having no luck so far. However, I will keep trying.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(BookingDateTime ReleaseDateTime)
1706819759999.9998 1849523760000.0002
     1720915380000 1839846059999.9998
1758474059999.9998      1862125020000
1767658139999.9998      1.8372657e+12
1769427779999.9998 1845648120000.0002
1771614179999.9998 1845295800000.0002
1772040720000.0002 1842602039999.9998
     1774294980000 1837502400000.0002
     1775503980000 1842207360000.0002
1780100040000.0002      1903910399000
1781891519999.9998      1882441320000
     1781999301000 1825250876000.0002
1784918179999.9998 1834787195999.9998
1785945779999.9998 1833801924000.0002
1785952260000.0002 1834312719999.9998
1786562536000.0002      1828704544000
     1786952682000 1828176272999.9998
1791228539999.9998 1863442260000.0002
     1791813180000 1833794464000.0002
1792341240000.0002      1842876120000
     1793027682000 1829471546000.0002
     1793466540000      1849793940000
1794891253000.0002 1832967565999.9998
1795292340000.0002      1903910399000
1796763060000.0002      1.8749151e+12
1797502833999.9998      1825321137000
1798843956999.9998 1832411671999.9998
1799121263000.0002 1832411790000.0002
1799492771000.0002 1831902490000.0002
1799680835999.9998 1828704477000.0002
     1.7999448e+12 1856769119999.9998
1800742785000.0002      1828705487000
1800804761000.0002 1831936479999.9998
1802271695999.9998 1830865593000.0002
     1802314620000 1834312638000.0002
     1802351847000 1827572411999.9998
1802636284999.9998 1827566621999.9998
1802955704999.9998      1825339365000
     1802959527000      1826096494000
     1803731340000 1836197999999.9998
     1804192524000 1826984970000.0002
     1804342860000 1843398060000.0002
1804610382999.9998 1826536495999.9998
1804776066999.9998 1831382654999.9998
     1805141668000      1825236673000
1805211024999.9998 1825836601999.9998
1805494320000.0002 1846250400000.0002
     1805765217000      1826377082000
     1805895304000 1829228506999.9998
1806505833999.9998 1831359255000.0002
     1806589602000 1826883435000.0002
1806686682999.9998 1827661562999.9998
1806792600000.0002      1836221760000
1806854940000.0002 1844372639999.9998
1806950636000.0002 1831038224999.9998
1806952648999.9998 1829472046999.9998
1807039080000.0002      1.8380391e+12
1807191993999.9998      1830177797000
     1807195548000 1833001702000.0002
1807481189000.0002      1828771385000
1807490999999.9998 1833549915000.0002
1807543020999.9998 1827587280000.0002
1808219459999.9998      1865151720000
     1808324478000      1834214025000
     1808952960000 1838638560000.0002
1808953219999.9998 1828950314999.9998
1809349979999.9998 1837848839999.9998
1810149539999.9998 1839243900000.0002
1810229897000.0002 1828099634000.0002
     1810329704000      1826984789000
1810340874000.0002 1829465853000.0002
     1810357126000 1831994481000.0002
     1810373983000      1826313051000
     1810462997000 1828705327000.0002
     1810514678000      1827418327000
     1811004199000 1829817256000.0002
     1811061022000 1825699592999.9998
1811194047000.0002      1831278456000
     1.8113598e+12 1834210613999.9998
     1811623572000      1830765673000
1811649080000.0002      1825744058000
1811768730999.9998      1825321234000
1811806358999.9998 1827394410999.9998
1812069503999.9998      1825324372000
     1812297840000      1836222540000
1812313323999.9998 1826394487999.9998
1812361875000.0002 1827907375999.9998
1812488557000.0002 1827522383000.0002
1812627900000.0002      1836657060000
     1812642660000      1860942420000
     1812848326000      1825693465000
1813079316000.0002 1826878446999.9998
1813236465000.0002      1825671077000
     1813291637000      1828184966000
     1813379841000 1825171038999.9998
1813534920999.9998 1826299979000.0002
     1813607093000 1826983052999.9998
     1813694668000 1831043548999.9998
     1813871862000      1825580135000
     1813961263000      1828176185000
end
format %tc BookingDateTime
format %tc ReleaseDateTime

I really appreciate any help you can provide.

[cu dao huy]

hi you may want to use these command below to create quarter for booking date time and release date time:

gen qbook = qofd(dofc(BookingDateTime))
gen qrelea = qofd(dofc(ReleaseDateTime))
format qbook qrelea %tq

you can substract qrelea to qbook to see how many quarter a participant gone through.
but which quarter in which year is very difference in interpretation

pardon me for potential typos.