topic Re: Handling Date & Time stamps across midnight in Discussions

Handling Date & Time stamps across midnight

JulieSAppel — Sat, 10 Jun 2023 23:26:03 GMT

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

Re: Handling Date & Time stamps across midnight

jthi — Fri, 12 Feb 2021 16:11:28 GMT

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

txnelson — Fri, 12 Feb 2021 16:46:16 GMT

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

Re: Handling Date & Time stamps across midnight

SDF1 — Fri, 12 Feb 2021 16:58:16 GMT

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

Mark_Bailey — Fri, 12 Feb 2021 18:16:14 GMT

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

peng_liu — Sun, 14 Feb 2021 02:12:45 GMT

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

JulieSAppel — Mon, 15 Feb 2021 12:06:26 GMT

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

Re: Handling Date & Time stamps across midnight

Ryan_Gilmore — Mon, 15 Feb 2021 17:11:12 GMT

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

Re: Handling Date & Time stamps across midnight

SDF1 — Mon, 15 Feb 2021 18:44:09 GMT

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!,