advantage-database-server

Query recent records with separate date and time fields

I'm working with a table in SAP Advantage with separate date and time fields. I want to find records with a date and time within the last 5 minutes.

This works 99% of the time:

SELECT
    *
FROM
    table_name
WHERE 
    TIMESTAMPDIFF(SQL_TSI_DAY, date_field, CURRENT_TIMESTAMP()) < 1
AND
    TIMESTAMPDIFF(SQL_TSI_MINUTE, time_field, CURRENT_TIMESTAMP()) < 5

However, this won't work around midnight. For instance, at 12:00AM, any records created at 11:57PM the previous day won't match the filter.

Any idea to do this? Thanks!

Sample image of data. Based on this data, at 7/12/19 at 12:01AM, I'd like to return the last 2 rows.

enter image description here

Created: 7/11/2019 22:54:43

Item EmpNo      LastName             FirstName       date_field time_field
--------------------------------------------------------------------------
1          2 Nelson               Roberto         7/11/2019  21:00:00  
2          4 Young                Bruce           7/11/2019  22:00:00  
3          5 Lambert              Kim             7/11/2019  23:00:00  
4          8 Johnson              Leslie          7/11/2019  23:56:00  
5          9 Forest               Phil            7/12/2019  00:00:00
Solution

  • The easiest way is to recombine the fields and then use TIMESTAMPDIFF():

    TRY DROP TABLE #test; CATCH ALL END TRY;

CREATE TABLE #test
(
    date_field DATE
  , time_field TIME
);

INSERT INTO #test
      SELECT '2019-07-11', '21:00:00' FROM system.iota
UNION SELECT '2019-07-11', '22:00:00' FROM system.iota
UNION SELECT '2019-07-11', '23:00:00' FROM system.iota
UNION SELECT '2019-07-11', '23:56:00' FROM system.iota
UNION SELECT '2019-07-12', '00:00:00' FROM system.iota
;

SELECT
  TIMESTAMPDIFF(SQL_TSI_MINUTE, 
      CREATETIMESTAMP(
          YEAR(date_field)
        , MONTH(date_field)
        , DAY(date_field)
        , HOUR(time_field)
        , MINUTE(time_field)
        , SECOND(time_field)
        , 0
      )
    , DATETIME'2019-07-12T00:00:00' --  CURRENT_TIMESTAMP()
  )
FROM #test;

    Which gives the expected result of:

    180
120
4
0

    It would be even more trivial if ADS supported an operator or a function to directly combine a date and a time, but I can't find one in the documentation.

    So if you integrate that into your original SQL code, it would be:

    SELECT
    *
FROM
    table_name
WHERE 
  TIMESTAMPDIFF(SQL_TSI_MINUTE, 
      CREATETIMESTAMP(
          YEAR(date_field)
        , MONTH(date_field)
        , DAY(date_field)
        , HOUR(time_field)
        , MINUTE(time_field)
        , SECOND(time_field)
        , 0
      )
    , CURRENT_TIMESTAMP()
  ) < 5