Handling Date & Time stamps across midnightHi all,
I´m working with data from an experiment where light and day has been switched around in a controlled environment.
In this case, over a certain period of time, light is turned on at 10 PM and turned off at 10 AM everyday. For each DateTime value (every 5 min) in this experiment I have created a column where I need to characterise if it´s light or dark.
I have created a script:
If( :LightsOnDate < :DateTime <= :LightsOffDate,
"Light phase",
"Dark phase"
)
When data in DateTime moves past 10 PM it correctly puts in the value "light phase", however when it moves past midnight it changes to "dark phase" again.
Why is it doing that and how can I fix it?
Br Julie
Data TableScriptingRe: Handling Date & Time stamps across midnightYour formula seems to working correctly. LightOnDate on last three rows is most likely incorrect as light was turned on the previous day?
Re: Handling Date & Time stamps across midnightHi @JulieSAppel ,
As @txnelson mentions, you have to deal with the time of day issue. I got it to work with the following change to your column formula. I chose the 22 because of the lights always go on at 22:00 hours. But, there are other ways this can be done.
If(
:LightOnDate < :DateTime <= :LightOffDate |
Date Difference( :DateTime, :LightOnDate, "Hour" ) == 22,
"Light Pahse",
"Dark Phase"
)
Re: Handling Date & Time stamps across midnightI think that you only need the :DateTime value since the light on and light off is always the same. Similar to @txnelson . If so, this expression is simpler.
t1 = Modulo( :DateTime, In Days( 1 ) );
If( In Hours( 10 ) < t1 <= In Hours( 22 ),
"Dark",
"Light"
);
Re: Handling Date & Time stamps across midnightThe following explains what's wrong. Every row in your data table falls into one of the two situations.
1) DateTime is on the left side of the interval defined by OnDate and OffDate
2) DateTime is between OnDate and OffDate.
The second situation always gives expected answer. The first situation will fail nearly half of the time.
Re: Handling Date & Time stamps across midnight@JulieSAppel , I think you are correct in that you can do the comparison of the date values. I agree with @jthi's response in that the formula you have is correct but the value for LightOnDate might not be.
