How to create a day/night factor from POSIXct variable

Question

I have a data table with a column of POSIXct date/times. I would like to create a column that specifies "day" or "night" based on the POSIXct timestamp. Day is defined in local time as between 05:30:00 and 20:00:00.

I have tried to create the new variable using an ifelse statement based on a column of "time of day" created using strptime, but end up with strange results.

Here is a simple example - setting it up as a data table to match my real dataset.

library(data.table)

SightingTime = c("2017-07-31 09:56:27 UTC", "2017-07-31 10:36:30 UTC", "2017-08-01 00:07:20 UTC","2017-08-01 01:31:00 UTC", "2017-08-01 10:38:23 UTC", "2017-08-01 21:13:06 UTC", "2017-08-02 15:13:30 UTC", "2017-08-02 18:05:28 UTC", "2017-08-02 21:04:08 UTC")
x=data.table(SightingTime)

First I extract the time of day from the date/time variable - I want this in local time because I'll specify sunrise/sunset in local time.

x$TOD = strftime(x$SightingTime, format="%H:%M:S",tz="America/Halifax")

I'm not sure why the new TOD variable is still in UTC even though I specify a different time zone.

Then a try to create a new variable with an ifelse statement

x$daynight = with(x,
           ifelse(TOD > 05:30:00 & TOD < 20:00:00, "Day", "Night"))

I've clearly got this bit incorrect as I get warning messages AND the results in the "day/night" column do not make sense.

What I'm hoping for is something like this.

             SightingTime      TOD daynight
1: 2017-07-31 09:56:27 UTC 06:56:27    Day
2: 2017-07-31 10:36:30 UTC 07:36:30    Day
3: 2017-08-01 00:07:20 UTC 21:07:20    Night
4: 2017-08-01 01:31:00 UTC 22:31:00    Night
5: 2017-08-01 10:38:23 UTC 07:38:23    Day
6: 2017-08-01 21:13:06 UTC 08:13:06    Day
7: 2017-08-02 15:13:30 UTC 12:13:30    Day
8: 2017-08-02 18:05:28 UTC 15:05:28    Day
9: 2017-08-02 21:04:08 UTC 18:04:08    Day

There are more issues than in the linked question. The conversion from character to POSIXct doesn't work, the tz of the character vector seems UTC and needs to be converted, time of day is not extracted correctly and the comparison in ifelse doesn't work since 05:30:00 is not a valid format (neither numeric nor character). I wrote an answer that is more complete than the linked one but can't post it now. I think this was closed by mistake... — JBGruber
@JBGruber My mistake, I reopened. Sorry for the inconvenience. — Henrik
Given that you use data.table, you may also try the ITime class and between: x[ , daynight := c("night", "day")[(as.ITime(SightingTime) %between% as.ITime(c("05:30:00", "20:00:00")) + 1)]] — Henrik

JBGruber JBGruber · Accepted Answer · 2019-01-12T21:30:12

Data

library(data.table)

SightingTime_chr = c("2017-07-31 09:56:27 UTC", "2017-07-31 10:36:30 UTC", "2017-08-01 00:07:20 UTC","2017-08-01 01:31:00 UTC", "2017-08-01 10:38:23 UTC", "2017-08-01 21:13:06 UTC", "2017-08-02 15:13:30 UTC", "2017-08-02 18:05:28 UTC", "2017-08-02 21:04:08 UTC")
x = data.table(SightingTime_chr)

Code

A couple of things were not quite right with your conversion to datetime (the format for example):

x$SightingTime = as.POSIXct(x$SightingTime_chr, format = "%Y-%m-%d %H:%M:%S", tz = "UTC")
attributes(x$SightingTime)$tzone <- "America/Halifax"

I would first convert the character strings to POSIXct and then convert to the America/Halifax timezone as the original vector seems to be in UTC datetime (omit the second step if I#m wrong).

x$TOD <- format(x$SightingTime, format="%H%M%S")

x$daynight = with(x, ifelse(TOD > "053000" & TOD < "200000", "Day", "Night"))

I convert the time of day to a pseudo-numeric value (the values are not technically correct but this should be enough for the comparison purpose).

x$daynight
[1] "Day"   "Day"   "Night" "Night" "Day"   "Day"   "Day"   "Day"   "Day"

Now the results appear correct.

Alternative

From this answer we can get an elegant solution in case we want mor than just night/day:

nightday <- function(datetime) {
  paste(
    c("Night", "Morning", "Afternoon", "Evening", "Night")[
      cut(as.numeric(format(datetime, "%H%M")), c(0, 530, 1100, 1700 ,2000, 2359))
      ]
  )
}
nightday(x$SightingTime)
[1] "Morning"   "Morning"   "Night"     "Night"     "Morning"   "Evening"   "Afternoon" "Afternoon" "Evening"

How to create a day/night factor from POSIXct variable

1 Answers

Data

Code

Alternative