SQLPage Initializing with an incorrect time.

[proleary1]

Does anyone know a way to correct this issue?

I started SQLpage server with a connection to a MYSQL database. It all works great except when I use the MYSQL Now() function it returns the wrong time. UTC timezone instead of EST. The system time on the machine is correct, as well as the system time on the MYSQL settings and the MYSQL server. The now() function returns the correct time via every other connection I use.

I see when I start SQLPage at the command line, that it shows the incorrect time. It should be: 2024-01-02T15:08:20.942

[2024-01-02T20:08:20.942Z INFO sqlpage::webserver::database::connect] Connecting to database: mysql://xxx:xxx@xxx/xx
[2024-01-02T20:08:21.170Z INFO sqlpage::webserver::database::migrations] No migration found in C:\website\sqlpage/migrations. You can specify database operations to apply when the server first starts by creating files in sqlpage/migrations/_.sql where is a number and is a short string.
[2024-01-02T20:08:21.171Z INFO sqlpage::webserver::http] Will start HTTP server on 0.0.0.0:8080
[2024-01-02T20:08:21.183Z INFO sqlpage] Server started successfully.
SQLPage is now running on http://127.0.0.1:8080
You can write your website's code in .sql files in C:\website.

Right now I am using an easy fix of Now() -5 hours but I want to understand what is happening. Any help is appreciated. Thanks!

[lovasoa]

Hello and welcome to SQLPage!

You can set the time zone on connection:
See https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html and https://github.com/lovasoa/SQLpage/blob/main/configuration.md#connection-initialization-scripts

You can create sqlpage/on_connect.sql with:

SET time_zone = 'America/New_York';

[E8y2FqZE]

There does not appear to be an equivalent of this in SQLite unfortunately. https://stackoverflow.com/questions/21756948/how-to-set-timezone-in-session-of-sqlite

I'm in a situation where I'm running on shared hosting and therefore unable to change the timezone of the host machine. The timezone is important, not just the UTC offset, because of terrible things like Daylight Savings Time in which my offset changes multiple times per year. Since these things are baked into the operating system and not SQLite, is it worth considering adding some SQLPage functions for getting the time in a particular timezone that works regardless of database quirks?

[lovasoa]

Hello ! Could you open a proper feature request with the solution you envision ?

Could you tell us more about the shared hosting solution you are using ? If you are interested, we can setup a hosted solution that will be better suited to your situation. Feel free to get in touch by filling the form on https://sql.ophir.dev/your-first-sql-website/hosted.sql . You'll get early access to sqlpage cloud.

[lovasoa]

If you have the ability to set environment variables, you can set TZ to let sqlite know your timezone

ophir@ophir:~$ export TZ='Europe/Paris'
ophir@ophir:~$ echo "select datetime('now', 'localtime')" | sqlite3
2024-07-07 22:06:54
ophir@ophir:~$ export TZ='America/New-York'
ophir@ophir:~$ echo "select datetime('now', 'localtime')" | sqlite3
2024-07-07 20:08:36

[E8y2FqZE]

Setting the environment variable works! Thank you!

[proleary1]

Thanks! That worked for me. I had to use this as my on_connect.sql in my particular setup:

SET @@SESSION.TIME_ZONE = "-5:00";