wrong time on chart component

[guiguid]

Introduction

There is a gap in the time in SQLITE, and "chart" component ( True as TIME ).

To Reproduce

List of steps to reproduce the behavior. Include the sql file you are using and the eventual relevant parts of your database schema

select 
dt                      as date
FROM balance
ORDER BY balance.dt DESC LIMIT 1;
-- RETURN
-- 2024-05-20 08:26:49

others component works (timeline component) :

select 
    'timeline' as component,
    TRUE       as simple;
select 
    'Last Update at ' as title,
    dt                      as date
FROM balance
ORDER BY balance.dt DESC LIMIT 1;

Actual behavior

chart component display wrong time :

select 
    'chart'             as component,
    'Balance History' as title,
    'area'              as type,
    'indigo'            as color,
    5                   as marker,
    TRUE                as time;

select 
    dt as x,
    balance                     as y 
FROM balance
ORDER BY balance.dt DESC LIMIT 60;

Expected behavior

The time displayed by "chart" component should be the same as SQLITE and others components.

Version information

• OS: NixOS
• Database : SQLite
• SQLPage Version : 0.20.5

Additional context

Local Time: GMT +1 (Paris) + Summer.

more

Thanks for this amazing tool!

[DSMejantel]

Bonjour,

You need ISO datetimes who are parsed and displayed in a readable format. I use this expression : strftime('%Y-%m-%dT%H:%M:%fZ',date_created) as x

select 
    'chart' as component,
    'Historique du solde' as title,
    'area'  as type,
    'red'   as color,
    1 as marker,
    TRUE as time,
    1000 as ymax;   
select 
    strftime('%Y-%m-%dT%H:%M:%fZ',date_created) as x,
    printf("%.2f",(SUM(prix) OVER(order by date_created))) as y
    FROM finances; 

[guiguid]

Thanks, this hack works!

But the documentation should be updated if not internally fixed in sqlpage it self.

[DSMejantel]

Re, C'est dans la documentation de SQLPage comme suggéré avec l'exemple 1.

Mais cela nécessite quelques connaissances dépassant SQLpage pour s'adapter le cas échéant au format des données de sa base. Cela pourrait être rappelé avec un autre exemple.

[guiguid]

Nice, I don't notice the subtility of " and ISO datetime are ....". Thx

@DSMejantel : Mais d'un autre coté, si "chart" n'accepte que des dates ISO, pourquoi ne pas forcer la conversion via sqlpage ? Car tout autre format de date sera sans interet. Pourquoi laisser ceci coté code utilisateur ?

[lovasoa]

Hello @guiguid, and welcome to SQLPage ! Out of curiosity: what is the application you are building ?

The provided datetime string does not have timezone information, and SQLPage interprets it as a local datetime, but displays it as UTC on the x axis by default. I do agree that this behavior is surprising and should be changed.

The default behavior should be to display the data in the user's local timezone when no timezone info is provided. I will change that for the next version of SQLPage.

However, if I may give a piece of unsolicited advice: you probably should not store local datetime strings without timezone information in your database. Such strings are inherently ambiguous. You may want to store either a datetime string with timezone information (such as 2024-05-20 08:26:49+02:00), or a much more concise unix timestamp (unixepoch(), 1716196946)

[guiguid]

Hi @lovasoa , I write a little web frontend for a mining wallet. remote host <-> ssh <-> periodic nuShell scripts for data processing (in and out) <-> SQLITE DB <-> SQLPage as Gui frontend.

As all my users are ( for now) in the same TZ, I basically store datetime in local time in SQLITE db (Yes, KISS for now...)

CREATE TABLE balance (
    dt DATE DEFAULT (datetime('now','localtime')),
    ....
 );

I'll give a try to your advise. Thanks

[lovasoa]

The behavior of SQLPage concerning x axes in time series plots was changed. You can have a look at the next (unreleased) version in lovasoa/sqlpage:main on docker.

[lovasoa]

Your app sounds cool! Do you have a link? Have you written about it?

[guiguid]

Not now. It's a private usage only, but I could write something about, that's a good idea.

[lovasoa]

I would love to read about it. And if you have pain points while learning SQLPage, I'm very interested in that too, I'm currently trying to simplify and clarify the website and documentation.

[guiguid]

I've point some things:

• How to draw a multilines charts (the purpose of "series" is un clear for me , and I have not found examples.
• Is it possible to add a "cron" feature in order to execute a .sql at given time or delay? (Perhaps in the file stored inside the DB with an additional column ?)
• make file stored inside the DB compatible with sqlar native format. It makes easy to store sql file in db with no extra cost. Thanks SQLite ! ( just rename sqlpage_files to sqlar, and add 2 columns) . So update sql file in main SQLite DB will be as easy as : sqlite3 sqlpage/sslpage.db -Au *.sql

Maybe I have to open issue for each items above ?

[lovasoa]

Thank you for the feedback!

1.I'll add a multi line plot example.

2. For cron, that could be a good idea. In the meantime, it's easy to just use the native cron with curl to execute a SQLPage page at regular intervals.

3. I didn't know sqlar, I'll look into it.