How to extract day, month, year, hour and minutes from a large integer

user_6209331 0
Viewed 107

In my raw data file (4600 records) date, year, hour and minutes are merged together in a large integer, example:

1205981254 (May 12, 1998, at 12:54)

The problem is that records for dates between day 10 and 31 of each month have 10 digits, while there are only 9 digits of dates between day 1 and 9:

905981254 (May 9, 1998, at 12:54)

I created this raw data file many years ago while being a student, and followed no particular format. How can I extract day, month, year, and time of day from these integers? I have read trough all the previous Qs and As without finding a solution of my particular problem.

r datetime
5 Answers

You can convert data back to POSIXct/POSIXlt format :

x <- c(1205981254, 905981254)
x1 <- as.POSIXct(sprintf("%010d", x), format = "%d%m%y%H%M", tz = 'UTC')
x1
#[1] "1998-05-12 12:54:00 UTC" "1998-05-09 12:54:00 UTC"

You can then extract whichever information you want from this.

#Date      
as.integer(format(x1, "%d"))
#[1] 12  9
#Hour
as.integer(format(x1, "%H"))
#[1] 12 12
#Minute
as.integer(format(x1, "%m"))
#[1] 5 5
user_39629140 
dates <- c( 1205981254, 905981254 )
#convert to character
dates <- as.character( dates )
#convert to posix, based on length.. add a 0 as prefix in case of 9 character-length
dplyr::if_else( nchar(dates) == 10, 
                as.POSIXct( dates, format = "%d%m%y%H%M"),
                as.POSIXct( paste0(0,dates), format = "%d%m%y%H%M") )

[1] "1998-05-12 12:54:00 CEST" "1998-05-09 12:54:00 CEST"
user_63562780

Maybe this works for you if the century of the year stays the same.

library(dplyr)
#> 
#> Attache Paket: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

x <- 905981254
y <- 1205981254


df <- data.frame(records = as.character(rep(c(x, y), 100))) %>% 
  mutate(records = ifelse(nchar(records) == 9, paste("0", records, sep = ""), records)) %>% 
  mutate(records = as.POSIXct(records, format = "%d%m%y%H%M"))
head(df)
#>               records
#> 1 1998-05-09 12:54:00
#> 2 1998-05-12 12:54:00
#> 3 1998-05-09 12:54:00
#> 4 1998-05-12 12:54:00
#> 5 1998-05-09 12:54:00
#> 6 1998-05-12 12:54:00

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

user_63248610

You can try this:

v1 <- '1205981254'
v2 <- '905981254'
#Extract dates first
nv1 <- as.Date(v1,'%d%m%y%H%M')
nv2 <- as.Date(paste0(0,v2),'%d%m%y%H%M')
#Extract hours
nh1 <- paste0(substr(v1,nchar(v1)-3,nchar(v1)-2),':',substr(v1,nchar(v1)-1,nchar(v1)),':00')
nh2 <- paste0(substr(v2,nchar(v2)-3,nchar(v2)-2),':',substr(v2,nchar(v2)-1,nchar(v2)),':00')
#Concatenate
ndate1 <- paste0(nv1,' ',nh1)
ndate2 <- paste0(nv2,' ',nh2)
#Define as dates
as.POSIXlt(ndate1,tz = 'GMT')
as.POSIXlt(ndate2,tz = 'GMT')

[1] "1998-05-12 12:54:00 GMT"
[1] "1998-05-09 12:54:00 GMT"
user_20808480

If all your years are 19XX and not 20XX you can use

dates <- c(1205981254,905981254)
as.POSIXct(sub("(..)(..)(..)(..)$","-\\1-19\\2 \\3:\\4", dates),format="%d-%m-%Y %H:%M")

 "1998-05-12 12:54:00 AST" "1998-05-09 12:54:00 AST"
user_104424230
Related