Query recent records with separate date and time fields

Question

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.

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

A `TIMESTAMP` field contains both a date and a time. If you didn't separate them, this issue wouldn't exist, because a simple `TIMESTAMPDIFF` against the current timestamp would do what you want. You've made this a pretty complicated task. How were the date and time columns initially stored? (Where did the individual values originate, and how were they written to the DB?) Can you [edit] to provide sample data and the output you'd like to obtain from that data? — Ken White, Jul 12 '19 at 02:19
I didn't create the table. They are 'date' and 'time' fields populated by a 3rd party system. — TommyGunn32, Jul 12 '19 at 02:27
OK. Again, can you provide sample data and the output you want from that data? — Ken White, Jul 12 '19 at 02:31
I can't use the data from an image. I can't copy/paste it into ARC32 and create a table from it using CREATE TABLE and INSERT. Right-click on a column header, choose to export to a text format and display it on screen, and then copy/paste that data with the column headings into your post. — Ken White, Jul 12 '19 at 02:50
Edited. Sorry, not familiar with SAP, and didn't expect someone to help this far. I formatted it as code, but it doesn't look like the columns lined up very well. Thanks again. — TommyGunn32, Jul 12 '19 at 02:57

Jens Mühlenhoff · Accepted Answer · 2019-07-12T09:09:20.187

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:

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

That's what I get for sleeping. Someone stole my thoughts on a solution. :-) That's how I was thinking it should be done too. +1. — Ken White, Jul 12 '19 at 12:45

Query recent records with separate date and time fields

1 Answers1