Timezone is ignored

[exactoph]

Hey,

When looking on the run overview of currently started executions, I see "In 2 hours" as start time. The tasks are running correctly, however it seems Semaphore stores the time in UTC and displays it in my timezone. I've configured the DB time to Europe/Berlin and also the server timezone.

Looking into a report of a task the very first line also has a timestamp in UTC, all following lines are correct in Europe/Berlin: 6:35:44 PM Task 35 added to queue 4:35:48 PM Preparing: 35

Searched in issues and found the still open issues #1042 and #974 ... Is there something wrong in the configuration or is this a bug in semaphore?

[Jieiku]

Same issue here.

The first line is the incorrect time, then the following times are correct... but if you come back to the task history later it seems to only show the incorrect time.

goto the dashboard, hold ctrl and press F5 to refresh the page, check the same task:

I can type date at the command line, and it is correct. I also set and verified the postgresql timzeone:

sudo -u postgres psql -c 'show timezone'

it is correct...

not sure what else to do.

This is important, because the start time is off, this makes the history in the dashboard incorrect, tasks that I ran only seconds ago show 7 hours ago:

OS is configured properly. If I type date at the command line, it shows me the correct date, time, and timezone.

The only thing I can think is that its incorrect someplace else.... this project uses go.... maybe we need to set an environment variable?

https://stackoverflow.com/questions/54363451/setting-timezone-globally-in-golang

[xpliz]

Sadly TZ env for docker doesn't work. Neither mounting /etc/timezone and /etc/localtime doesn't help.

[Jieiku]

My install is not a docker install, I have it installed in a proxmox LXC container where timezones should work without issue, despite that, my time in semaphore is off by 7 hours.

If I type date at the command line, it shows me the correct date, time, and timezone.

[Jojonintendo]

Just leaving some info here that worked for me.

I had to actually export /etc/localtime in both containers, semaphore and the DB. This way, cron actually respects my timezone.

[Jieiku]

does the time appear correct in your semaphore web ui dashboard now? for instance mine immediate after running a task says that I ran it 7 hours ago.

when you say you had to export /etc/localtime how exactly did you do that? add it to .bashrc?

If you could give a quick rundown on how to do this, I would appreciate it.

I found your post: https://github.com/ansible-semaphore/semaphore/issues/1042#issuecomment-1609059495

seems your using docker, I am not.

[Jojonintendo]

Sorry for not having specified it. Yes, I use a container environment with Podman.

The hour was always correct for me in the web ui dashboard, however inside the containers it was not. I would see the correct hours of execution, but cron would actually work with an offset.

[germbow]

I was able to observe the same issue in my "Semaphore" implementation. To correct this problem I have modified the file "web/src/main.js" replacing line 15: Vue.filter('formatDate', (value) => (value ? moment.utc(String(value)).local(true).fromNow() : '—')); By: Vue.filter('formatDate', (value) => (value ? moment(String(value)).local(true).fromNow() : '—'));

With this modification, after generating the binary again, I was able to verify that the execution times and the corresponding calculations (start time) are correctly displayed in the "Semaphore" web interface.

Server:

On the client:

• Note: this is an automatic translation from Spanish to English using google translate.

[radarsugarcrm]

Hi, I was pointing this out already in Feb 2022 (https://github.com/ansible-semaphore/semaphore/issues/859).

would it be possible to change this line in code, please? Because I do not like to fiddle around in the source code.

many thx in advance

[KamaranL]

TL;DR

I performed an in-place upgrade to 2.9.64 from a clean install of 2.7.22 after my attempt at a clean install of 2.9.64 was producing this exact issue.

Story-time

I'm not exactly sure how I got this working, BUT I will say that I was experiencing the EXACT same issue with a fresh install of the latest semaphore. Fortunately, I'm still in the setup/testing phase of evaluating this application, so destroying and rebuilding the setup from scratch isn't hurting anyone.

While combing through some of the previous issues regarding the incorrect time being displayed in the UI, I did come across #859, where @radarsugarcrm pointed out that 2.7.22 did not have this issue. So, I thought great, I'll just use 2.7.22 and wait until they fix this issue in the latest version. I thought this was my solution until I ran the setup for semaphore 2.7.22 and it would error every time it ran the db migrations with mariadb as a provider...

Enter #681, which described my exact error with the db migration. A good fellow that goes by ziptx pointed out that there were some errors in the db migration queries and provided a workaround SQL script- which worked great after I fixed some syntax errors in the end portion (migrations).

I was now on 2.7.22, which then revealed an inconvenience (to me)- this version didn't allow you to run playbooks from the local file system like 2.9.64 does. I also lost the ability to notify MS teams which was going to be helpful as well.

So... I removed semaphore using rpm -e semaphore, which kept it's dependencies installed, and then installed the latest version using rpm as well, rpm -i semaphore_<version>_<arch>.rpm. After running the semaphore server command while pointing to my existing config file, it performed another db migration and continued to run. I set everything back up as I had it with the fresh install of the latest, and it's working as expected showing the correct date and time everywhere.

---

System details
OS	CentOS Stream 9
MariaDB	v11.3.2
Git	v2.43.0
OpenSSH	v8.7p1

[Jieiku]

Gut wrenching pain just thinking about trying to do all that to fix the time issue. Our install still has this issue, so maybe I will try something similar, however we are running Ubuntu so it may not even solve the problem.

[hrobayo]

In the case you are using Postgres as DB the problem is with the definition of datetime column in .sql files, it is converted in "Timestamp without timezone" data type in postgres, when the system encode to json response it returns with UTC time "2024-05-08T11:56:55.253614Z", I'm not expert in go, but maybe it using some lib to convert sql response to json, and the column is serialized in that string... so, manually change all columns to "timestamp with time zone" solves the issue for me....

ALTER TABLE public.task__output ALTER COLUMN time TYPE timestamp with time zone ;

PD.: be sure to have the correct timezone configured in both the app server and the db server.

In postgres...

SELECT current_setting('TIMEZONE')

In app

cat /etc/timezone

[Jieiku]

Thanks @hrobayo cannot wait to try that!

will report back if it solves it!

[ace-kzimm]

Running into this issue as well. Installed from rpm binary on RHEL 9. I've setup /etc/timezone. Postgres show correct timezone. Host shows correct timezone. Semaphore appears to be in UTC. Running 2.10.11-d31ebd3-1719394739

[toxic0berliner]

same issue here, running the latest semaphore inside docker, passed /etc/localtime to both the postgres db and the semaphore containers, no luck, went into the database to change the collumn type with ALTER TABLE public.task__output ALTER COLUMN time TYPE timestamp with time zone ; but still no luck.

Running a task now and it continues to tell me it has been executed "in 2 hours"

Anyone with a fix please share.

[wntrstn]

I have the same problem.

I configured TZ: "Europe/Berlin" as environment variable for the semaphore container and TZ: "Europe/Berlin" as well as PGTZ: "Europe/Berlin" for the postgres container.
Running date returns the correct time in both containers. But in the web interface, a task that just finished shows up as starting "in 2 hours".

[zeridon]

The problem lies indeed in the incorect column type. This appears to be happening with postgres only. Fixing it is prety simple:

List all columns of the wrong type (change semaphore to the name of the DB)

select
    col.table_schema,
    col.table_name,
    col.ordinal_position as column_id,
    col.column_name,
    col.data_type,
    col.datetime_precision
from
    information_schema.columns col
join
    information_schema.tables tab
on
    tab.table_schema = col.table_schema
    and
    tab.table_name = col.table_name
    and
    tab.table_type = 'BASE TABLE'
where
    col.data_type in ('timestamp without time zone')
    and
    col.table_schema not in ('information_schema', 'pg_catalog')
order by
    col.table_schema,
    col.table_name,
    col.ordinal_position;

For every listed column alter the type to timestamp with timezone (ex)

ALTER TABLE public.event ALTER COLUMN created TYPE timestamp with time zone ;

Or if you are feeling brave (and trust me implicitly):

select concat('ALTER TABLE ', col.table_schema, '.', col.table_name, ' ALTER COLUMN ', col.column_name, 'TYPE timestamp with timezone;')
from information_schema.columns col
join information_schema.tables tab
on tab.table_schema = col.table_schema and tab.table_name = col.table_name and tab.table_type = 'BASE TABLE'
where col.data_type in ('timestamp with time zone') and col.table_schema not in ('information_schema', 'pg_catalog') \G

[zeridon]

Probably the best fix is to change timestamp to timestamptz in the migrations handling https://github.com/semaphoreui/semaphore/blob/develop/db/sql/migration.go#L87

Anyways storing timestamp without timezone is asking for trouble.

[hmoffatt]

I configured TZ: "Europe/Berlin" as environment variable for the semaphore container and TZ: "Europe/Berlin" as well as PGTZ: "Europe/Berlin" for the postgres container. Running date returns the correct time in both containers. But in the web interface, a task that just finished shows up as starting "in 2 hours".

I just set TZ: Australia/Melbourne here on my Semaphore container (Postgres was already with the local timezone). The dashboard now shows a job that has just run as starting "in 10 hours", but at least it ran at the scheduled time. Previously jobs ran 10 hours after they were scheduled.

[nroach44]

I did @zeridon 's steps (manually, the \G disagreed with my version) and it all works as expected. This is a direct package install, not a container.