0

The TDengine interval(1n) query returned one more result than expected. TDengine version 3.0

Executed SQL

SELECT
    _wstart,
    _wend,
    LAST ( point_value ) - FIRST ( point_value ) AS point_value 
FROM
    configuration.p413611955640541184
WHERE
    ts >= "2023-07-01 00:00:00" and ts <="2023-08-03 13:57:31" interval(1n) FILL(PREV)

Expected results

         _wstart         |          _wend          |        point_value        |
================================================================================
 2023-07-01 00:00:00.000 | 2023-08-01 00:00:00.000 |            1503.300000000 |
 2023-08-01 00:00:00.000 | 2023-09-01 00:00:00.000 |            1503.300000000 |

Actual results

         _wstart         |          _wend          |        point_value        |
================================================================================
 2023-06-01 00:00:00.000 | 2023-07-01 00:00:00.000 |                      NULL |
 2023-07-01 00:00:00.000 | 2023-08-01 00:00:00.000 |            1503.300000000 |
 2023-08-01 00:00:00.000 | 2023-09-01 00:00:00.000 |            1503.300000000 |

enter image description here

The actual results exceeded the expected results, as recorded in June But changing the query time to June to July will not have any extra records

enter image description here

Does this seem to be related to the long month and short month? Is this a bug?

interval(1n,8h) Executed SQL

    SELECT _wstart, _wend, LAST ( point_value ) - FIRST ( point_value ) AS point_value FROM configuration.p413611955640541184 WHERE ts >= "2023-06-01 00
    :00:00" and ts <="2023-08-03 13:57:31" interval(1n,8h) FILL(PREV);

Actual results

         _wstart         |          _wend          |        point_value        |
================================================================================
 2023-05-01 08:00:00.000 | 2023-06-01 08:00:00.000 |             190.800000000 |
 2023-06-01 08:00:00.000 | 2023-07-01 08:00:00.000 |           17211.700000000 |
 2023-07-01 08:00:00.000 | 2023-08-01 08:00:00.000 |            1359.500000000 |
 2023-08-01 08:00:00.000 | 2023-09-01 08:00:00.000 |            1359.500000000 |
MrZao
  • 3
  • 2

1 Answers1

0

please try:

SELECT
    _wstart,
    _wend,
    LAST ( point_value ) - FIRST ( point_value ) AS point_value 
FROM
    configuration.p413611955640541184
WHERE
    ts >= "2023-07-01 00:00:00" and ts <="2023-08-03 13:57:31" interval(1n,8h)  FILL(PREV)

In TDengine the time windows start from UTC-0 1970-1-1 0:0:0.000, we need to use offset to control the timezone.

BoP
  • 2,310
  • 1
  • 15
  • 24
55615612
  • 11
  • 3
  • I have read similar content on the webpage address https://xie.infoq.cn/article/5f9e7ec100bbeb41401a36cb7. WIth interval(1n,8h), database queries will still return an extra result – MrZao Jul 03 '23 at 09:10
  • I have tried the SQL you provided above, and the results are at the end of the text above – MrZao Jul 03 '23 at 09:18
  • date -R Mon, 03 Jul 2023 17:25:05 +0800 – MrZao Jul 03 '23 at 09:25
  • you can try to add the 'ts' column after _wend, and try again and show me the output – 55615612 Jul 06 '23 at 06:48