Hello,
I am trying to fetch data from the PeerTube database. I am in the timezone ‹ Asia/Kathmandu › i.e, +05:45. When I run the query directly in database using 3rd party app like DBeaver. I get the latest data from the database as shown here.
However, when i send the response of the same query to PeerTube. If the time is from 00:00:00 to 05:44:59, I get the response (value added to previous date) as shown here in the peertube console. Is PeerTube restricting the result in core?? I am building a plugin and for fetching data i am using PeerTube hooks await peertubeHelpers.database.query
When i run the following command. The UTC time is printed
let date_ob = new Date().toUTCString() ;
console.log("This is time::: ", date_ob);
Can this be the problem?? As the terminal is running nodejs the time is different so, I am getting this wrong output?
What is your SQL request?
Date operation made in the requests uses the Postgresql timezone.
To check the postgresql timezone, you can execute select NOW();.
I also thought that the request are based on Postgresql timezone. However, as you can see on the image above, When i run the query on database using terminal or 3rd party database manager. I get the result and the response with sql query using plugin gives the response different. For instance, i am running this demo 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
and v.uuid = $videoId
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), date(localtimestamp), interval '1 day')
as tdt(day_inp)
)
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
Peertube uses sequelize for requests. Maybe it is sequelize that handle the timezone.
Inside the PeerTube core, the server tests shows the same approach as i have done in the above query.
In the test, they have not used the sequelize for requests.
peertubeHelpers.database.query is Sequelize under the hood:
Have you tried to do select NOW() with peertubeHelpers.database.query to check what timezone is used?
Hey!!
I ran select Now() inside peertubeHelpers.database.query. Here you can see the details of the output given in terminal.
- Localtime the query was executed.
- The query that are being executed.
- The current time given from query.
- The Timezone (i.e., 00 difference).
*My timezone is UTC +5:45.
If the peertubeHelpers.database.query is sequelized under the hood. It should give me the result as shown in the postgres database in the question. Am i wrong to assume, that this problem is not related to query but rather with the PeerTube core or node?
I don’t really work with postgresql since I finished my studies, 18 years ago. I don’t really know how postgresql handles timezones. I read that data are always stored in UTC and translated when queried (that make sense).
I think (but i’m not sure) that the sequelize instance initiated by peertube does not specify any timezone, so it uses UTC by default.
You should search if it is possible to specify another timezone for one postgresql request.
WARNING: don’t change the tz for the whole connection, this could have major side effects!
Note: I don’t know what you try to achieve, but dealing with tz can easily become a mess. In theory, you should work in utc for calculations, and only change the tz when you display the data to a user. Maybe you can simplify the problem you are trying to solve by changing the way you work.
2 examples of how you can simplify some problems:
- use UTC, even for the displayed data. Are these 5 hours of shift really an issue?
- instead of grouping your data by days, group them by hours. Then you can easily deals with tz.
That’s just 2 examples of different approaches. I don’t know if they are acceptable in your case.
I know you are busy @Chocobozzz.
But it would be helpful if you can shed some light on this one please?
Did you read the postgresql documentation as I suggested?
The date_trunc function you are using can take a timezone as 3rd parameter:
