CODEWITHSUNDEEP
rdatetimelubridate
Will Hauser
Will Hauser

Reputation: 197

R posixct dates and times not centering on midnight

I have dates and times stored in two columns. The first has the date as "20180831." The time is stored as the number of seconds from midnight; 3am would be stored as 10,800.

I need a combined date time column and am having a hard time with something that should be simple.

I can get the dates in no problem but lubridate "hms" interprets the time field as a period, not a 'time' per se.

I tried converting the date to posix.ct format and then using that as the origin for the time field but posix.ct does not set the time for midnight, instead it sets it for either 1800 or 1900 hours depending on the date. I need it set to midnight for all rows, I don't want any daylight savings time adjustment.

Here's the code: First I made a function because there are several date and time fields I have to do this for.

mkdate<-function(x){
a<-as.Date(as.character(x),format='%Y%m%d')
a<-as.POSIXct(a)
return(a)
}

df$date<-mkdate(df$date) #applies date making function to date field 

df$datetime<-as.POSIXct(df$time,origin=df$date)

I'm sure this has to do with time zones. I'm in Central time zone and I have experimented with adding the "tz" specification into these commands in both the mkdate function and in the time code creating "datetime" column. I've tried:

tz="America/Chicago"
tz="CST"
tz="UTC"

Help would be much appreciated!

Edited with example:

x<-c(20180831,20180710,20160511,20170105,20180101) #these are dates.

as.POSIXct(as.Date(as.character(x),format="%Y%m%d"))

Above code converts dates to seconds from the Jan 1 1970. I could convert this to numeric and add my 'seconds' value to this field BUT it is not correct. This is what I see instead as the output:

[1] "2018-08-30 19:00:00 CDT" "2018-07-09 19:00:00 CDT" "2016-05-10 19:00:00 CDT" "2017-01-04 18:00:00 CST" "2017-12-31 18:00:00 CST"

Look at the first date - it should be 8/31 but instead it is 8/30. Somewhere in there there is a timezone adjustment taking place. It's moving the clock back 5 or 6 hours because I am on central time. The first entry should be 2018-08-31 00:00:00. I would then convert it to numeric and add the seconds field on and convert back to POSIXct format. I've tried including tz specification all over the place with no luck.

Sys.getlocale("LC_TIME")

returns "English_United States.1252"

Upvotes: 4

Views: 1143

Answers (2)

Onyambu
Onyambu

Reputation: 79288

There are very many ways to do this:

mktime = function(a, b)modifyList(strptime(a, '%Y%m%d'), list(sec = as.numeric(gsub(',', '', b))))

 mktime("20180831",'10,800')
 [1] "2018-08-31 03:00:00 PDT"
mktime('20180301','10800')
[1] "2018-03-01 03:00:00 PST"
 mktime('20180321','10800')
[1] "2018-03-21 03:00:00 PDT"

Looking at the above code, it does not adjust for the daylight saving time. Irrespective of the date, the seconds still show that it Is 3 AM, including the dates when ST-->DT. This will also take into consideration, your LOCAL timezone.

Upvotes: 1

Rui Barradas
Rui Barradas

Reputation: 76585

I believe the following does what you want.
My locale is the following, so the results are different from yours.

Sys.getlocale("LC_TIME")
#[1] "Portuguese_Portugal.1252"

The difference will be due to the daylight savings time, the summer hour.

As for your problem, all you have to do is to remeber that the objects of class "POSIXct are coded as the number of seconds since an origin, and that origin is usually the midnight of 1970-01-01. So you have to add your seconds since midnight to the seconds of as.Date.

x <- "20180831"

xd <- mkdate(x)
y <- 10800

as.POSIXct(as.integer(xd) + y, origin = "1970-01-01")
#[1] "2018-08-31 04:00:00 BST"

as.POSIXct(as.integer(xd) + y, origin = "1970-01-01", tz = "America/Chicago")
#[1] "2018-08-30 22:00:00 CDT"

Upvotes: 1

Related Questions