Setting Timezone

[accforgithubtest]

I want to set the timezone for sqlpage container, so the UI timestamps are displayed correctly in local times instead of UTC. I have tried the below and none of them have worked, can you pls help me understand how to set the timezone correctly ? (DB is mysql if that changes anything.)

Can this be an open issue to request that the common ways of specifying the timezones via an environment variable or volume mounts (/etc/timezone) be supported ?

Things that have not worked so far -

    environment:
      TZ: "Australia/Sydney"
      TIME_ZONE: "Australia/Sydney"
      TIMEZONE: "Australia/Sydney"
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /etc/timezone:/etc/timezone:ro
      - ./sqlpage/on_connect.sql:/sqlpage/on_connect.sql
      - ./:/var/www

Also setting up the on_connect.sql didnt work

SET TIME ZONE 'Australia/Sydney';
SET TIME_ZONE 'Australia/Sydney';
SET TZ 'Australia/Sydney';
SET TIME_ZONE = 'Australia/Sydney';
SET TZ = 'Australia/Sydney';

[lovasoa]

Hi! You may be interested in this discussion: https://github.com/lovasoa/SQLpage/discussions/170#discussioncomment-8003925

[accforgithubtest]

Thanks for your reply. I missed to include above that I had already seen and tried this SET @@SESSION.TIME_ZONE = "+11:00"; as well. and both suggestions in 170 did not work for me.

Am I doing something wrong with how on_connect.sql needs to be made available to sqlpage ? I tried by both mounting a volume, as well as placing a sqlpage/on_connect.sql under /var/www, and both of them did not work.

- ./sqlpage/on_connect.sql:/sqlpage/on_connect.sql

[lovasoa]

I suspect you are using docker and mounting the sqlpage folder at the wrong place.

Here is a complete working example with docker-compose, which you seem to be using:

# Two containers: a simple mysql database and a SQLPage instance that
# uses it.

# The database container
services:
  db:
    image: mysql:8
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: sqlpage
      MYSQL_USER: sqlpage
      MYSQL_PASSWORD: sqlpage
    ports:
      - "3306:3306"
  sqlpage:
    image: lovasoa/sqlpage:main
    environment:
      DATABASE_URL: mysql://sqlpage:sqlpage@db/sqlpage
    volumes:
      - ./src:/var/www
      - ./sqlpage:/etc/sqlpage
    ports:
      - "8080:8080"

❯ tree
.
├── docker-compose.yml
├── sqlpage
│   └── on_connect.sql
└── src
    └── index.sql

You should see the following line in the sqlpage logs:

sss-sqlpage-1  | [2024-01-05T10:19:40.037Z INFO  sqlpage::webserver::database::connect] Creating a custom SQL database connection handler from "/etc/sqlpage/on_connect.sql"

[accforgithubtest]

Thanks for your reply !

As suspected, I was not passing on_connect.sql correctly to sqlpage. This below mapping in docker compose file fixed it for me.

- ./sqlpage:/etc/sqlpage