How do I combine year month date time into a single datetime column using DBI in R?-CodePudding

Here's an example of my source data csv, df:

    Year             : int  2005 2005 2005 2005 2005 2005 2005 2005 2005 2005 ...
    Month            : int  1 1 1 1 1 1 1 1 1 1 ...
    DayofMonth       : int  28 29 30 31 2 3 4 5 6 7 ...
    Time             : int  1605 1605 1610 1605 1900 1900 1900 1900 1900 1900

How can I combine the 4 columns into a single column into a datetime column in my database using SQLite and not dplyr?

I hope the output can be something like: 2005-01-29 09:30:00 so that I can plot graphs.

I hope my qn makes sense.

CodePudding user response：

You could use lubridate::make_datetime:

Year =c(2005, 2005, 2005)
Month = c(1,2,3) 
DayofMonth = c(  28, 28, 30)
Time = c(1605, 1605, 1610)


lubridate::make_datetime(Year,Month,DayofMonth,floor(Time/100),Time%0)

[1] "2005-01-28 16:05:00 UTC" "2005-02-28 16:05:00 UTC" "2005-03-30 16:10:00 UTC"

CodePudding user response：

Base R, using Waldi's sample data (thanks!):

with(df, as.POSIXct(sprintf("%i-i-%i i:i:00", 
  Year, Month, DayofMonth, Time %/% 100, Time %% 100))
)
# [1] "2005-01-28 16:05:00 EST" "2005-02-28 16:05:00 EST" "2005-03-30 16:10:00 EST"

This is (possibly naïvely) assuming that you never have seconds or fractional minutes or Time values that are not "meaningful" (i.e., more than 59 minutes, more than 23 hours).

Data

df <- structure(list(Year = c(2005, 2005, 2005), Month = c(1, 2, 3), DayofMonth = c(28, 28, 30), Time = c(1605, 1605, 1610)), class = "data.frame", row.names = c(NA, -3L))