Page 1 of 2

Another daylight saving issue

Posted: Wed Mar 14, 2018 7:14 am
by Batrams
11.1.5 Linux, PHP 5.6.33
Timezone for all employees is set to America/New York which is correct for my location. Schedules have changed from 9AM-5PM to 10AM-6PM. Recurring schedules templates say 9-5. Can I fix this? Time and timezone on the server/OS are correct

Re: Another daylight saving issue

Posted: Wed Mar 14, 2018 8:24 am
by shaunw
If you edit then save one of your recurring schedules, without making any changes, does it correct the problem for you on future scheduled shifts?

Also do you have "system_timezone" specified in your timetrex.ini.php file at all?

Re: Another daylight saving issue

Posted: Fri Jun 08, 2018 12:12 pm
by pribis
Same issue I think. I am on 11.0.2 using php 5.6.

Timezone is correct on the server, but all of the dates for pay periods is set to GMT and are thus four hours off. I tried setting the ssytem_timezone in the timetrex.ini, but the only option it appears to accept is EST. Which is only right for half the year. Technically we are EDT. But that just causes pay periods to default back to GMT.

We don't use reoccuring schedules. They are all set to manual.

I'm running out of ideas.

Re: Another daylight saving issue

Posted: Fri Jun 08, 2018 12:22 pm
by pribis
nm, found it: EST5EDT Not EDT.

Re: Another daylight saving issue

Posted: Thu Aug 23, 2018 6:02 am
by pribis
I upgraded our postgres install to 11.2.5 from 11.0.2. But in both the timezone was GMT for all of our pay periods.

I set the system_timezone = EST5EDT in the conf file and have confirmed that this is being picked up by the TTLDate class.

My system time is set to EST as well, but if I don't set system_timezone it sets it to GMT.

America/New York is what is set in my personal preferences.

system_timezone once worked, but now it doesn't seem to be.

Re: Another daylight saving issue

Posted: Thu Aug 23, 2018 8:35 am
by mikeb
When you edit your pay period schedule, under the "Adcanced" tab, you can specify the timezone that is used for it there. Its completely separate from the "system_timezome" setting in the .ini file.

Re: Another daylight saving issue

Posted: Thu Aug 23, 2018 8:44 am
by pribis
They are set to EST5EDT.

For the record, these were all correct before doing the conversion from MySQL to postgres.

Re: Another daylight saving issue

Posted: Thu Aug 23, 2018 8:57 am
by pribis
No matter what I set the timezone to, under the advanced tab, nothing changes. The dates are still GMT.

Re: Another daylight saving issue

Posted: Thu Aug 23, 2018 9:02 am
by mikeb
Changing the timezone won't change historical pay period times, it just changes future pay period times.

If the problem occurred after the MySQL -> PostgreSQL conversion, its likely there was a problem during that step. Step #4 of our conversion FAQ viewtopic.php?f=6&t=7519 is critical to ensuring the timezones don't get mixed up, by forcing the timezone to UTC in both MySQL and PostgreSQL, thus preventing any discrepancies.

Have you checked your punch times to ensure they are all proper too? Usually if one set of data is incorrect, other sets are too.

Re: Another daylight saving issue

Posted: Fri Aug 24, 2018 4:43 am
by pribis
Are you telling me that the dates and times are NOT stored in the db as GMT and then converted for display?

The punch times are all correct. This appears to be the only place this is happening.

I did step #4 exactly (I cut and pasted, changing only the un, pw and db name).

Re: Another daylight saving issue

Posted: Fri Aug 24, 2018 4:54 am
by pribis
I just checked our original db, and these dates and times are in fact stored as GMT. So this is not a problem with storage, this is a problem with the display. For some reason they are not being changed to EST.

I checked the postgres records and they are also in GMT and after comparing some of the records, they are the same as the original db.

Re: Another daylight saving issue

Posted: Fri Aug 24, 2018 5:46 am
by pribis
Further, on my original mysql install, if I change my account preference's timezone, ALL of the pay period times update to the new timezone setting. This is NOT the case with the new install.

Re: Another daylight saving issue

Posted: Fri Aug 24, 2018 5:48 am
by pribis
The original install is at 10.6.1, if I haven't said that already.

Re: Another daylight saving issue

Posted: Fri Aug 24, 2018 6:26 am
by pribis
I created a new pay period and even though it is GMT in the database, it displays correctly in TT. Why? I checked the start and end times in the database for the new record and a previous one and there is no difference (well, except the date). Both are GMT. So what makes the old record display wrong and the new one correctly?

Simply doing this with the live site isn't really an option, as it involved deleting two previous pay periods to put the new one in (which is four hours earlier than the old ones).

Re: Another daylight saving issue

Posted: Fri Aug 24, 2018 6:33 am
by pribis
I am TRULY sorry for all these posts. Really I am. But...

On the postgres install, even though the times are four hours in the future (GMT), if I open the pay period and check the Audit entry, it is correct! The times are in EST5EDT.

Re: Another daylight saving issue

Posted: Fri Aug 24, 2018 8:20 am
by mikeb
Can you please post a screenshot of Payroll -> Pay Periods using the PostgreSQL database so we can see the specific times.

Re: Another daylight saving issue

Posted: Fri Aug 24, 2018 9:55 am
by pribis
Included pay period screenshot and one for the audit of the first pay period.

Re: Another daylight saving issue

Posted: Fri Aug 24, 2018 10:09 am
by pribis
I went through exporting the data from mysql and importing to postgres into version 11.0.2 just to see if somehow I missed anything. But the results were the same. The dates and times on the pay periods are correct in the mysql version of 11.0.2 but not in the postgres version. Upgrading to 11.2.5 doesn't help, of course.

Re: Another daylight saving issue

Posted: Mon Aug 27, 2018 9:55 am
by pribis
Of course, the audit messages would be correct. Not sure what I was thinking there. But what was the point of the other pic? This crazy problem is keeping me from upgrading.

Re: Another daylight saving issue

Posted: Mon Aug 27, 2018 10:25 am
by pribis
Ok, so I made a mistake. Any new pay periods are created in the specified system timezone. All the old ones are GMT. I suppose I could fix this with the right SQL, but I'm wondering when this was suppose to be converted.

Re: Another daylight saving issue

Posted: Mon Aug 27, 2018 11:16 am
by pribis
Checking the old mysql database and the times are EST5EDT. Not sure why I thought it was different. It appears like the export from mysql for postgresql changed all the times to UTC (for pay_periods, not timesheets). Is this possible?

Re: Another daylight saving issue

Posted: Mon Aug 27, 2018 12:31 pm
by mikeb
I think it may be related to the fact that MySQL doesn't actually have a concept of timestamps without timezones like PostgreSQL, so the two are getting confused.

Make sure you have a backup of your PostgreSQL database and run the following queries on your PostgreSQL database to see if it fixes the pay period dates/times:

Code: Select all

BEGIN;
WITH pp_tz AS (
    SELECT
      pp.*,
      (extract(TIMEZONE FROM pp.start_date :: TIMESTAMP AT TIME ZONE pps.time_zone) :: TEXT ||
       ' secs') :: INTERVAL AS utc_offset
    FROM pay_period AS pp
      LEFT JOIN pay_period_schedule AS pps ON (pp.pay_period_schedule_id = pps.id)
)
UPDATE pay_period
SET
  start_date       = (pay_period.start_date + pp_tz.utc_offset),
  end_date         = (pay_period.end_date + pp_tz.utc_offset),
  transaction_date = (pay_period.transaction_date + pp_tz.utc_offset)
FROM pp_tz
WHERE pay_period.id = pp_tz.id;

SELECT
  pp.*,
  pps.time_zone,
  (extract(TIMEZONE FROM pp.start_date :: TIMESTAMP AT TIME ZONE pps.time_zone) :: TEXT ||
   ' secs') :: INTERVAL AS utc_offset
FROM pay_period AS pp LEFT JOIN pay_period_schedule AS pps ON (pp.pay_period_schedule_id = pps.id)
ORDER BY pp.id;
The last query will show you the new data, so you can confirm the dates are correct, if they are run "COMMIT;" to commit the changes, if they aren't run "ROLLBACK;" to rollback without making any changes.

Re: Another daylight saving issue

Posted: Tue Aug 28, 2018 5:13 am
by pribis
I'm getting:
ERROR: relation "pay_period" does not exist
LINE 6: FROM pay_period AS pp

Re: Another daylight saving issue

Posted: Tue Aug 28, 2018 5:22 am
by pribis
Ugh! Sorry. Forgot to select my database.

Re: Another daylight saving issue

Posted: Tue Aug 28, 2018 5:26 am
by pribis
Definitely looks MUCH better. Thank you for sticking with me.