Inputing daylight to datetime


I have a binary time data set that gives me the sunset and sunrise hours. 1 is day, 0 is night. It is ordered by date and time (without year). I’d like to input 1 (day) or 0 (night) to a given date&time (this time including year). Any hint?

01/01 - 08:17 | 1
01/01 - 17:33 | 0
01/02 - 08:18 | 1
01/02 - 17:33 | 0
01/03 - 08:18 | 1
01/03 - 17:34 | 0
01/03 - 08:19 | 1

Given value: 2021/01/02 13:10:01 should get a 1 as it is during daylight.
Given value: 2021/01/02 22:10:15 should get a 0 as it is night.

Thank you

Hi @jquadrada,

assuming your table cover a whole year, i would solve it with like followed

a) split the reference table (datatable) in month, day and time
b) pivot the table to have daylight start and night start in one row
c) extract month and day and time as well for your datetime stamp of interest
d) join the pivoted tabel and the target table via keys month and day
e) check if time is between night start and night start (1=day) or not (0=night)

For step (e) i would implement additional steps e.g. creating datatime columns combining the date of the target table and the timmes of day start and night start. There are probably other (easier) solutions which came to the same solution.


1 Like

Hi morpheus,

I understand your solution but it seems complicated to implement. I have no single value to check, I have a huge list of datetimes so I think it will be dificult to manage it like this. Ideally I should compare the date and time value of each row and give a 0 or a 1 deppending of the daylight table…

Hi @jquadrada,
Knime allways process all records! That means if you split the cell entry 01/01 - 08:17 in month, day and time β€˜01’, β€˜01’, β€˜08:17’. This also happens for all other records.
The joiner in step 4 matches both table together and therefore you get for each record the corresponding day start time and night start time which you then can further process.

OK. I’m struggling with the pivoting (quite new in Knime). Could you lease give me some advice how to configure it?

The pivot settings are
group columns: day, time
pivot column: your column containing (0 and 1) for day and night
Manual aggregation: time (aggregation type: first)
advanced settings (optional):
column name: Pivot name
aggregation name: Keep original name(s)

your outcome should look like
month, day, 0, 1,
β€˜01’, β€˜01’, β€˜17:33’, β€˜08:17’
β€˜01’, β€˜02’, …


1 Like

Cool, thank you. I work this out.
Thanks again!!!

Well I found a quite simple solution:

  • create a daylight table (from the beginning of my date data)
  • join both tables (data and daylight table)
  • merge date columns
  • sort ascending by merged date columns
  • missing values on the daylight column, with previous vaule

And I get 1 or 0 for each row :slight_smile:


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.