Handling Date & Time stamps across midnight

[#1]

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

[#2]

Your formula seems to working correctly. LightOnDate on last three rows is most likely incorrect as light was turned on the previous day?

[#3]

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 align

[#4]

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"
)

[#5]

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"
);

[#6]

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.

[#7]

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 

[#8]

@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.

[#9]

Hi @JulieSAppel ,

  I believe this should work and do it for any time difference between the on/off states.

If(
	Modulo( :LightOnDate, In Days( 1 ) ) > Modulo( :DateTime, In Days( 1 ) ) & 	Modulo( :DateTime, In Hours( 1 ) ) == Modulo( :DateTime, In Days( 1 ) ),"Light Phase",
	:LightOnDate < :DateTime <= :LightOffDate, "Light Phase",
	"Dark Phase"
)

  Should be independent of whether or not the on/off times are 1 hr, 12 hs, or 6 hrs apart.

  I'm not sure how your setup is recording the data, but if it's basically pulling a time stamp from equipment and has some way to record (even if just a Boolean) the state of the equipment, e.g. 0==off and 1==on, then you would only need two columns, one would be the actual day/time column and the other would be the Boolean state of the system and you can set the Value Labels to 0 == "Dark Phase" and 1 == "Light Phase", for example. If you can do it that way, then you wouldn't need to worry about issues of going over midnight or even how long each phase is. Even if there's a user interface button that one clicks to change phases, the program should be able to record the state of the button and feed it to an output table with the date/time data as well.

Hope this helps!,

DS