Search code examples
rsqlitedbi

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

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.

Solution

  • 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%%100)

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