358498_en-US

liliconcom

Handling Date & Time stamps across midnight

Hi 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 midnight

Your 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 midnight

Given that your on and off times are static, I believe the formula should be

If( Hour( :DateTime - In Hours( 10 ) ) < 12,
 "Dark phase",
 "Light phase"
)

One needs to deal with the hour of the day, and the subtracting off of the 10 hours forces the days to allign

Re: Handling Date & Time stamps across midnight

Hi @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 midnight

I 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 midnight

The 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

Thanks for all of your replies. I see the issue with midnight, but I just thought that as DateTime is basically just a numerical value that needs to be evaluated whether it is in a range or not, it wouldn´t be a problem but I guess I was wrong.

 

I see the different solutions working for this specific data but I´m looking for a generic solution (I probably should have mentioned that) that will fit different datasets where LightsOn and LigthsOff are different than what they are here. 

 

So for other datasets, LightsOn could be at 7 AM and LightsOff at 9 PM meaning that the different periods may also vary in length. 

 

Is there anyway to get around this?

 

Br Julie