Unable to get latest date from the Peertube query

[raazkarkee405]

Describe the current behavior

Using peertube plugin hook peertubeHelpers.database.query I tried to get the video views on a generated series basis with a day interval between the video upload date to the current date. On querying the DB using Postgres itself I get the latest data till the current date but on PeerTube response the date returned is 1 day behind today's date if I query after 00:00;00 and before +5:45. I am in the timezone Asia/Kathmandu.

Also, if I get a view between the time interval my view is added to the previous day.

My issue is similar to this: https://framacolibri.org/t/unable-to-get-latest-data-from-peertube-query/15469

Steps to reproduce

1. Create a plugin.
2. Set the timezone to Asia/Kathmandu and set the time between 00:00:00 and 05:44:59
3. Add the following query using peertubeHelpers.database.query

   with lifetime_views_cte(
       upload_date,
       total_views) as (
   select
   vv."startDate" ::date as upload_date ,
   sum(vv."views") as total_views
   from
   video v
   join "videoChannel" vc on
   vc.id = v."channelId"
   join "account" a on
   a.id = vc."accountId"
   join "user" u on
   u.id = a."userId"
   join "videoView" vv on
   vv."videoId" = v.id
   where
   u.id = $userId
   group by
   u.id,
   v.id,
   vv."startDate"
         ),
       cross_joined_cte(day_inp) as (
   select
   date(date_trunc('day', tdt.day_inp::date))
   from
   generate_series((select min(date(u."createdAt")) from "user" u where u.id = $userId), localtimestamp  at time zone 'UTC', interval '1 day')
         as tdt(day_inp)
   order by
   1
         )
         select
   cjt.day_inp::date as date,
   sum(coalesce (lvc.total_views,
         0)) as value
   from
   cross_joined_cte cjt
   left join lifetime_views_cte lvc
         on
   cjt.day_inp = lvc.upload_date
   group by
   cjt.day_inp
   order by
   cjt.day_inp

4. Check the result both in Postgres and PeerTube.

Describe the expected behavior

The result should be the same in both postgres and peertube.

Additional information

• PeerTube instance:

  • URL: Private
  • Version: 4.3.0
  • NodeJS version: 16+
  • Postgres version: 12+

• Browser name, version and platforms on which you could reproduce the bug:

• Link to browser console log if relevant:

• Link to server log if relevant (journalctl or /var/www/peertube/storage/logs/):

[Chocobozzz]

Please don't open duplicated topics since there's already one on the forum: https://framacolibri.org/t/unable-to-get-latest-data-from-peertube-query/15469/9 where you haven't answered to John's anwser.