CODEWITHSUNDEEP
rtimestamp
Billaus
Billaus

Reputation: 91

R extract Date and Time with Timezone

I originally asked a simpler question on this here and the answer worked fine. However, I now need to do the same thing with a dataset from another country for which I need change the timezone for consistency with my analysis.

So here's a row from my df:

df
      Date            Name    Surname   Amount
2015-07-24 23:15:07   John     Smith     200

Let's say I want to convert to timezone +8 hours and extract time and date in different columns. Here's what I'd like it to be:

df1
       Date           Year  Month   Day    Day_w   Hour Minute Seconds  Name    Surname   Amount
2015-07-24 23:15:07   2015    7     25    Saturday  07    15     07     John     Smith     200

As you can see the original Date column remains the same, but the new column automatically converts the time (7am from 11pm) and also changes the day if necessary (in this case from 24 to 25; and from what was supposed to be Friday to Saturday).

I suppose I can first convert the whole Date column to the new timezone and then split dates and times, but how do I make it change the day automatically if the time requires it to be so (like in my example)?

Thanks!

Upvotes: 2

Views: 5186

Answers (4)

phiver
phiver

Reputation: 23598

You can use the package lubridate. And there is anarticle on revolution analytics about this.

library(lubridate)
library(dplyr)

df <- data.frame(Date = ymd_hms("2015-07-24 23:15:07"), Name = "John", Surname = "Smith", Amount = 200, stringsAsFactors = FALSE)
# date in UTC
df$Date
"2015-07-24 23:15:07 UTC"

# show timezone if you go to +8 hrs, from UTC / GMT to Shanghai.
# df$Date is not changed into new timezone
with_tz(df$Date, tzone = "Asia/Shanghai")
"2015-07-25 07:15:07 CST"    

# create output df and for each extraction change timezone
df <- df %>% mutate(year = year(with_tz(df$Date, tzone = "Asia/Shanghai")),
                    month = month(with_tz(df$Date, tzone = "Asia/Shanghai")),
                    day = day(with_tz(df$Date, tzone = "Asia/Shanghai")),
                    hour = hour(with_tz(df$Date, tzone = "Asia/Shanghai")),
                    minute = minute(with_tz(df$Date, tzone = "Asia/Shanghai")),
                    second = second(with_tz(df$Date, tzone = "Asia/Shanghai")))
# print df
df

                 Date Name Surname Amount year month day hour minute second
1 2015-07-24 23:15:07 John   Smith    200 2015     7  25    7     15      7

Upvotes: 1

Billaus
Billaus

Reputation: 91

Ok, I think I found an answer. Assuming my times are recorded in Sydney time and I want to convert it to UK time:

#Assigns new column as date

df$Date1 <- as.POSIXct(df$Date, tz="Australia/Sydney")

#Convert it to desired time

attributes(df$Date1)$tzone <- "Europe/London"

It worked fine for me, so I thought I'd post it in case others needed it too.

Upvotes: 0

Kartheek Palepu
Kartheek Palepu

Reputation: 972

library(lubridate)
# For Current date and time
d = now()
d = force_tz(d, "America/Chicago")
hour(d) = hour(d)+8;
df1 = data.frame(Date = d, Year = year(d),     Month = month(d), Day = day(d), Day_w = wday(d,label=T), Hour = hour(d), Minute = minute(d),     Seconds = second(d), Name ="John", Surname = "Smith", Amount=200)
print(df1);

Upvotes: 1

Ricky
Ricky

Reputation: 4686

I assume your date is in a string format, i.e d <- "2015-07-24 23:15:07".

You can convert it to date based on your local timezone with as.POSIXct.

> a <- as.POSIXct(d)
> a
[1] "2015-07-24 23:15:07 MYT"

(MYT is my local timezone)

And you can convert the same date-time in another timezone specifying tz, e.g.

> b <- as.POSIXct(b, tz="Japan")
> b
[1] "2015-07-24 23:15:07 JST"

For you can convert them to other time zones by changing the tzone attribute. E.g. I want to convert my first variable with local time to the same timezone as the second one

> attr(a, "tzone") <- "Japan"
> a
[1] "2015-07-25 00:15:07 JST"

(If you want to change to your local time zone, set the tzone to "")

If you want to extract just the time, use format

> format(a, "%H")
[1] "00"

or the full date time

> format(a, "%Y-%m-%d %H:%M:%S")
[1] "2015-07-25 00:15:07"
> format(b, "%Y-%m-%d %H:%M:%S")
[1] "2015-07-24 23:15:07"

Upvotes: 3

Related Questions