Another daylight saving issue

General support regarding TimeTrex, such as
configuring policies/taxes or processing payroll.
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: Another daylight saving issue

Post by mikeb »

If you use TimeTrex for payroll, you will need to run this query as well to fix the pay stub dates:

Code: Select all

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_stub
SET
  start_date       = (pay_stub.start_date + pp_tz.utc_offset),
  end_date         = (pay_stub.end_date + pp_tz.utc_offset),
  transaction_date = (pay_stub.transaction_date + pp_tz.utc_offset)
FROM pp_tz
WHERE pay_period_id = pp_tz.id;
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
Post Reply