Date Conversion from Posixct to Date in R

Date conversion from POSIXct to Date in R

The problem here is timezones - you can see you're in "HKT". Try:

as.Date(as.POSIXct("2013-01-01 07:00", 'GMT'))
[1] "2013-01-01"

From ?as.Date():

["POSIXct" is] converted to days by ignoring the time after midnight
in the representation of the time in specified timezone, default UTC

Unexpected date when converting POSIXct date-time to Date - timezone issue?

Using the setup in the Note at the end we can use any of these:

# same date as print(x) shows
as.Date(as.character(x))
## [1] "2020-03-24"

# use the time zone stored in x (or system time zone if that is "")
as.Date(x, tz = attr(x, "tzone"))
## [1] "2020-03-24"

# use system time zone
as.Date(x, tz = "")
## [1] "2020-03-24"

# use system time zone
as.Date(x, tz = Sys.timezone())
## [1] "2020-03-24"

# use indicated time zone
as.Date(x, tz = "Asia/Calcutta")
## [1] "2020-03-24"

Note

We have assumed this setup.

Sys.setenv(TZ = "Asia/Calcutta")
x <- structure(1584988320, class = c("POSIXct", "POSIXt"), tzone = "")

R.version.string
## [1] "R version 4.0.2 Patched (2020-06-24 r78745)"

Converting selected columns from POSIXct to Date with particular format using apply function

We select the range of columns which we want to format and then convert those into dates by using as.Date and then format it based on our requirement.

start_col <- which(colnames(df) == "t1_date")
end_col <-  which(colnames(df) == "t5_date")
df[start_col:end_col] <- lapply(df[start_col:end_col], 
                        function(x) format(as.Date(x), "%d-%b-%Y"))


df
# A tibble: 5 x 6
#  ID    t1_date     t2_date     t3_date t4_date t5_date
#  <chr> <chr>       <chr>       <chr>   <chr>   <chr>  
#1 001   23-Mar-2017 18-Apr-2018 NA      NA      NA     
#2 002   17-Oct-2017 NA          NA      NA      NA     
#3 003   23-Nov-2017 NA          NA      NA      NA     
#4 004   26-May-2018 NA          NA      NA      NA     
#5 005   24-Jan-2017 09-Apr-2018 NA      NA      NA    

---

The same can also be achieved with dplyr , mutate_at

library(dplyr)
df %>%
  mutate_at(vars("t1_date":"t5_date"), funs(format(as.Date(.), "%d-%b-%Y")))


#  ID    t1_date     t2_date     t3_date t4_date t5_date
#  <chr> <chr>       <chr>       <chr>   <chr>   <chr>  
#1 001   23-Mar-2017 18-Apr-2018 NA      NA      NA     
#2 002   17-Oct-2017 NA          NA      NA      NA     
#3 003   23-Nov-2017 NA          NA      NA      NA     
#4 004   26-May-2018 NA          NA      NA      NA     
#5 005   24-Jan-2017 09-Apr-2018 NA      NA      NA  

Problem with changing format of date and time in R

From the manual help(strftime):
If the specified time is invalid (for example ‘"2010-02-30 08:00"’) all the components of the result are ‘NA’.

Furthermore, if you want to manipulate your output format of a date you need the date stored as a Date-class (illustration of @izyda's comments).

First, reformat your date to make it easier to manipulate:

data <- "20170929 20:59:56.149"
dat_new <- paste( paste( substr(data, 1, 4), 
   substr(data, 5, 6), substr(data, 7, 8), sep="-" ), 
   substr(data, 10,nchar(data)) )
dat_new
[1] "2017-09-29 20:59:56.149"

Then, change the class to Date:

dat_cor <- as.POSIXct( dat_new, tz="GMT" )
dat_cor
[1] "2017-09-29 20:59:56 GMT"
class(dat_new)
[1] "character"
class(dat_cor)
[1] "POSIXct" "POSIXt"

Finally, choose your output format:

strftime( dat_cor, format="%m/%d/%Y %H:%M:%OS3", tz="GMT" )
[1] "09/29/2017 20:59:56.148"
# or
strftime( dat_cor, format="%Y-%d-%m %H:%M:%OS3", tz="GMT" )
[1] "2017-29-09 20:59:56.148"

Converting date in R with as.POSIXct() returns NA

This is a locale problem, months in your language have other names than in English (for %B in date format), that's why it fails. It simply cannot recognize "July" in apollo string as a month, because it searches for month names in your language.

Try to set English locale for dates and times by running:

Sys.setlocale(category = "LC_TIME", locale = "English")

or set English locale for all categories (monetary, numeric etc.):

Sys.setlocale(category = "LC_ALL", locale = "English")

For details, see Sys.setlocale()).

See this example (my default locale is Czech, so your code returns NA in my case as well):

apollo <- "July 20, 1969, 20:17:39"
apollo.fmt <- "%B %d, %Y, %H:%M:%S"
xct <- as.POSIXct(apollo, format = apollo.fmt, tz = "UTC")
xct
#> [1] NA

Sys.setlocale(category = "LC_TIME", locale = "English")
#> [1] "English_United States.1252"

apollo <- "July 20, 1969, 20:17:39"
apollo.fmt <- "%B %d, %Y, %H:%M:%S"
xct <- as.POSIXct(apollo, format = apollo.fmt, tz = "UTC")
xct
#> [1] "1969-07-20 20:17:39 UTC"

^{Created on 2020-07-18 by the reprex package (v0.3.0)}

---

Related Topics