R - Date/Time Calculations

Question

My Question is divided into 2 parts:

1st part:

I have a function, getdata() which I use to pull information for a date range.

get_data <- function (fac_num, start_date, end_date) {

  if (!(is.null(fac_num) | is.null(start_date) | is.null(end_date))) {

  if(end_date - start_date > 7) {
    start_date <- end_date - 7

  #start_date <- as.Date('2017-07-05')
  #end_date <- as.Date('2017-07-06')
  #fac_num <- "005"
  }

  new_start_date <- paste0(start_date,' 05:00:00')
  new_end_date <- paste0(end_date + 1,' 05:00:00')


  qry <- paste0("SELECT FAC_NUM, USER_ID, APPL_ID, FUNC_ID, ST_ID, NXT_ST_ID, RESP_PRMT_DATA,
                ST_DT_TM, END_DT_TM, RESP_PRMT_TY_CDE,
                REQ_INP_DATA FROM OPSDBA.STG_RFS_INTERACTION WHERE TRANS_ST_DT_TM >= DATE'",
                start_date,"' AND TRANS_ST_DT_TM BETWEEN TO_TIMESTAMP('",new_start_date,"', 'YYYY-MM-DD HH:MI:SS') AND TO_TIMESTAMP('",new_end_date,"', 'YYYY-MM-DD HH:MI:SS') 
                AND APPL_ID='CTS' AND FAC_NUM='",fac_num,"'")

and then I perform calculations on it.

Further, in my program. I use this getdata() function to pull data for a new set of analysis.

rf_log_perform <- get_data(display_facility_decode(input$facNum2), 
input$dateRange2,  input$dateRange2 + 1)

Here since I am using just a single date instead of range, I have added one to the range so that the getdata() function would work.

I then wanted to modify the date range in such a way that, it does not show anything past 11:59 for the selected date.

rf_log_perform$date <- ifelse(strftime(rf_log_perform$st_dt_tm, format="%H:%M:%S")<'05:00:00',
  format(as.POSIXct(strptime(rf_log_perform$st_dt_tm - 1*86400 , '%Y-%m-%d %H:%M:%S')),format = '%Y-%m-%d'),
  format(as.POSIXct(strptime(rf_log_perform$st_dt_tm , '%Y-%m-%d %H:%M:%S')),format = '%Y-%m-%d'))

By using the getdata() function, I would be able to pull data for date range 08/29/2017, 05:00:00 to 08/30/2017, 05:00:00 which is considered to be a day in my example.

But for my calculations, I want to discard everything which is beyond 08/29/2017, 11:59:59 PM, for more accurate results. For this purpose, I have added an ifelse statement in there to sort that out. But this isn't behaving as I expect and am confused on why not.

Answer 1

Unfortunately I still can not comment on the main question.

I encourage you to make two adjustments to your question to improve the chances on getting an answer to your question:

1) Please make your example reproducible e.g. provide date ranges, wrap your code in a well defined function etc.

2) Explain what you are trying to achieve. What is your intention and expected result.