How to compare datetime in r?

General
1

library(tidyverse)
How to compare datetime in r?
There is a date time variable in my dataset, want to use it as a condition to create a new variable, but got error message.
mydata<- mydata %>% mutate(Modified=ifelse(loaddate> '2020-05-20 14:00:00', "Updated", "not updated")
what is wrong? Thank you!

2

That value is a character string, not a "Date Time" object, see this example:

# This is a "character" string
class("2020-05-20 14:00:00")
#> [1] "character"

# This is a "Date Time" object
class(as.POSIXct("2020-05-20 14:00:00"))
#> [1] "POSIXct" "POSIXt"

Created on 2020-05-26 by the reprex package (v0.3.0)

If you need more specific help, please provide a proper REPRoducible EXample (reprex) illustrating your issue.

3

I am going to define a condition operation, how to do that? for example:

newdata<- newdata %>% mutate(Positive=ifelse(update> '2020-05-29 08:10', "Positive", "Negative")
Thank you!

4

You are not providing a reproducible example, please read the guide on the link I gave you before and try to make one.

5

mydata<- read.table(text="ID Lastupdate
A '2020-04-29 08:02:00'
B '2020-05-20 09:04:12'
C '2020-05-18 10:23:14'
D '2020-05-06 12:00:45'
E '2020-05-10 13:21:45' ", header=T)
How about use this sample data to create a new variable updatecondition:

mydata<- mydata %>% mutate(updatecondition=ifelse(Lastupdate > 2020-05-10 08:00:00, 'Yes', 'No')

Thank you!

6

As I said you have to use dates instead of characters, see this example

library(dplyr)

mydata<- read.table(text="ID Lastupdate
A '2020-04-29 08:02:00'
B '2020-05-20 09:04:12'
C '2020-05-18 10:23:14'
D '2020-05-06 12:00:45'
E '2020-05-10 13:21:45' ", header=T)

mydata %>% 
    mutate(Lastupdate = as.POSIXct(Lastupdate),
           updatecondition = ifelse(Lastupdate > as.POSIXct("2020-05-10 08:00:00"), 'Yes', 'No'))
#>   ID          Lastupdate updatecondition
#> 1  A 2020-04-29 08:02:00              No
#> 2  B 2020-05-20 09:04:12             Yes
#> 3  C 2020-05-18 10:23:14             Yes
#> 4  D 2020-05-06 12:00:45              No
#> 5  E 2020-05-10 13:21:45             Yes

Created on 2020-05-29 by the reprex package (v0.3.0)

1 Like
7

Thank you very much for your help! my dataset has many records, part of assigned value is wrong, should be "Yes" instead of "NO", don't know why.

8

I just printed the datetime, there is a UTC included in the value, like '2020-04-29 08:02:00 UTC', can this also use as.POSIXct to convert? why part of assigned value for the new variable is wrong? Thank you!

9

Please provide a reproducible example of your issue, otherwise, I don't have enough information to help you.

10

dataset are not allowed to upload here, if copy and paste, format changed

11

The dput() function is used to make a text representation of R data. Often head() is used to limit the portion of the data passed in.

1 Like
12

Thank you! I will try......

13

Here is the data:
structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,
45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60,
61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89), Update = structure(c(1590702578,
1589206396, 1589805622, 1590665185, 1590623139, 1585246983, 1590741938,
1590740931, 1590738637, 1590305832, 1590665713, 1590671225, 1590685086,
1588148977, 1590654068, 1590593567, 1590736985, 1585247220, 1585247230,
1590669904, 1590655528, 1590741098, 1590586486, 1590594852, 1587615463,
1585247359, 1590722190, 1590726094, 1585247401, 1590699799, 1585247420,
1586499572, 1585247447, 1590738267, 1590718523, 1590714443, 1590703076,
1585565823, 1590741475, 1585565962, 1590671383, 1590687194, 1590742656,
1586534488, 1590667665, 1590666826, 1590661791, 1587985666, 1590510810,
1590594535, 1586258493, 1589963558, 1590511622, 1588244652, 1590679041,
1586364925, 1590681897, 1586882653, 1590659784, 1585568768, 1590664003,
1588145315, 1585568774, 1590660698, 1590693825, 1590684888, 1585568837,
1590737011, 1587716271, 1590667290, 1590589902, 1590678225, 1585569202,
1585569229, 1585569273, 1590736573, 1590741431, 1590674289, 1590741439,
1589217448, 1590580103, 1590664418, 1590592445, 1590671513, 1590682850,
1590695504, 1590594533, 1589808203, 1590584700), class = c("POSIXct",
"POSIXt"), tzone = "UTC")), row.names = c(NA, -89L), class = c("tbl_df",
"tbl", "data.frame"))

input<- input %>% mutate(Update=as.POSIXct(Update),UpdateStatus=ifelse(Update > '2020-05-28 09:00:00',"Yes","No"))

14

Which part? Can you give us a specific example of this?

15

Update between '2020-05-28 09:00:00' and '2020-05-28 12:00:00' should be "Yes", but some of them was assigned "No".

16

I can't reproduce your issue, If I run the code with your sample data everything above '2020-05-28 09:00:00' is assigned "Yes".

As I said, please read the reprex guide I gave you and try to make a proper reproducible example that actually shows your issue.

17

Please forget it, probably is my computer system problem.
UTC is 5 hours ahead of CDT, after adjusting the cutoff of comparison, issue was solved. Thank you.

18

probably because you havent cast '2020-05-28 09:00:00' to a POSIXct class. so they are being compared as character strings I would guess

19

I did, but the system treat '2020-05-28 09:00:00' as CDT, others from my dataset as UTC.

20

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