Another daylight saving issue

General support regarding TimeTrex, such as
configuring policies/taxes or processing payroll.
Batrams
Posts: 45
Joined: Mon Aug 18, 2008 9:11 am

Another daylight saving issue

Post by Batrams » Wed Mar 14, 2018 7:14 am

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

shaunw
Posts: 7483
Joined: Tue Sep 19, 2006 2:22 pm

Re: Another daylight saving issue

Post by shaunw » Wed Mar 14, 2018 8:24 am

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?

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Jun 08, 2018 12:12 pm

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.

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Jun 08, 2018 12:22 pm

nm, found it: EST5EDT Not EDT.

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Thu Aug 23, 2018 6:02 am

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.

mikeb
Posts: 593
Joined: Thu Jul 27, 2006 11:58 am

Re: Another daylight saving issue

Post by mikeb » Thu Aug 23, 2018 8:35 am

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.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Thu Aug 23, 2018 8:44 am

They are set to EST5EDT.

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

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Thu Aug 23, 2018 8:57 am

No matter what I set the timezone to, under the advanced tab, nothing changes. The dates are still GMT.

mikeb
Posts: 593
Joined: Thu Jul 27, 2006 11:58 am

Re: Another daylight saving issue

Post by mikeb » Thu Aug 23, 2018 9:02 am

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.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Aug 24, 2018 4:43 am

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).

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Aug 24, 2018 4:54 am

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.

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Aug 24, 2018 5:46 am

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.

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Aug 24, 2018 5:48 am

The original install is at 10.6.1, if I haven't said that already.

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Aug 24, 2018 6:26 am

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).

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Aug 24, 2018 6:33 am

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.

mikeb
Posts: 593
Joined: Thu Jul 27, 2006 11:58 am

Re: Another daylight saving issue

Post by mikeb » Fri Aug 24, 2018 8:20 am

Can you please post a screenshot of Payroll -> Pay Periods using the PostgreSQL database so we can see the specific times.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Aug 24, 2018 9:55 am

Included pay period screenshot and one for the audit of the first pay period.
Attachments
Screenshot Audit 2018-08-24 12-53-18.png
Screenshot Audit 2018-08-24 12-53-18.png (250.34 KiB) Viewed 2669 times
Screenshot from 2018-08-24 12-52-35.png
Screenshot from 2018-08-24 12-52-35.png (340.16 KiB) Viewed 2669 times

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Fri Aug 24, 2018 10:09 am

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.

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Mon Aug 27, 2018 9:55 am

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.

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Mon Aug 27, 2018 10:25 am

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.

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Mon Aug 27, 2018 11:16 am

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?

mikeb
Posts: 593
Joined: Thu Jul 27, 2006 11:58 am

Re: Another daylight saving issue

Post by mikeb » Mon Aug 27, 2018 12:31 pm

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.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Tue Aug 28, 2018 5:13 am

I'm getting:
ERROR: relation "pay_period" does not exist
LINE 6: FROM pay_period AS pp

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Tue Aug 28, 2018 5:22 am

Ugh! Sorry. Forgot to select my database.

pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: Another daylight saving issue

Post by pribis » Tue Aug 28, 2018 5:26 am

Definitely looks MUCH better. Thank you for sticking with me.

Post Reply