Accrual Used has Different Date than TimeSheet

[Yajasi]

We are using TimeTrex 7.1.2 on a Ubuntu 12.04.1 LTS server. We have an Absence policy called 'Cuti Tahunan' tied to an Accrual policy called 'Sisa Cuti Tahunan'. When we enter an absence on a TimeSheet, the Accrual policy deducts the time absent from the Accrual bank using the wrong date. In these screenshots you can see that the absence was entered on 14-Oct but the accrual transaction date is 13-Oct.

All of our employees preferences are set to GMT, but I suspect that this is happening because we have not yet added the system_timezone parameter to the timetrex.ini.php file as detailed in this post:http://forums.timetrex.com/viewtopic.php?f=2&t=5695.

Is the missing system_timezone parameter the problem? If we change the parameter in the timetrex.ini.php file, will all of our TimeSheets punches change?

[shaunw]

I would start by setting the system_timezone in your timetrex.ini.php file. It will not change any of your punches, it will just affect things going forward.

[Yajasi]

I tried setting the system_timezone value in timetrex.ini.php to match all of our employee preferences (which are GMT).

Code: Select all

system_timezone=GMT

This has caused even more problems. When the user creates a New Absence, the system now overrides the date chosen by the user and places the absence on the day before.

With NO system_timezone parameter the user chooses Oct 14th as the date absent and gets the following results:

• Absense date is Oct 14
  Accrual Date is Oct 13

WITH system_timezone parameter the user chooses Oct 14th as the date absent and gets the following results:

• Absense date is Oct 13
  Accrual Date is Oct 13

I also tried setting the date.timezone value in the php.ini file on the server to GMT. This had no noticeable effect on the way TimeTrex calculated it's dates for accruals.

How do we get the absence date and the accrual date to be the same?

[shaunw]

I'm not able to replicate this on our end setting my timezone to GMT even with the system_timezone set to PST8PDT or GMT.

The only thing I can think of is that all your employee preferences aren't actually set to the same value, or some other issue much deeper.

Are you able to replicate the issue on our public demo, located here:
http://demo.timetrex.com/interface/flex ... sword=demo

Can you attach a copy of your TimeTrex log file immediately after you create the absence entry?

[Yajasi]

We found that our Linux server has a system timezone of GMT+09. When we change the timetrex.ini.php file to have the system_timezone value of GMT, all of our Timesheets get changed to very strange times, presumably nine timezones away.

Is there anyway to set the timetrex.ini.php file to GMT and then reset our PostgreSQL database tables to not have any timezone information in them? I think if we were to do that all of our dates and times would match. What tables to we need to update?

[shaunw]

Changing the system_timezone shouldn't cause your timesheet punch times to change at all unless the timezone set in each employees preferences is invalid and TimeTrex is falling back to the system_timezone instead.

Can you attach a screenshot of your MyAccount -> Preferences?

[Yajasi]

With this setting

Code: Select all

;system_timezone=GMT

we get:

and:

With this setting

Code: Select all

system_timezone=GMT

we get:

and:

... which is not what we want because those aren't the times the person came and left.

Our phpinfo date section has the following parameters:

• date/time support = enabled
  "Olson" Timezone Database Version = 0.system
  Timezone Database = internal
  Default timezone = Asia/Jayapura

And our php.ini file has the date.timezone parameter commented out.

All records in the timetrex database, table punch, field time_stamp show the format YYYY-MM-DD HH:MM:SS+09.

On the server terminal we can type 'date' and we get today's date and time with a timezone of EIT which is the same as the Asia/Jayapura timezone parameter from PHP.

[shaunw]

There is definitely something wrong then, TimeTrex only falls back to the system_timezone when it can't set the timezone for defined in each employees preferences... You will need to enable debug logging in the timetrex.ini.php and attach the log here.

You can find information on doing that here: http://forums.timetrex.com/viewtopic.php?f=6&t=89

[Yajasi]

Log is attached.

App Version: TimeTrex 7.1.2
Database: phpPgAdmin 5.0.4
WebServer: Apache/2.2.22
Server: Ubuntu 12.04 LTS
PHP Version 5.3.10-1ubuntu3.9 (error logging off)

[shaunw]

According to the log, the default system timezone in use is: Asia/Jayapura

Then the employees timezone is set to GMT.

Do you have system_timezone defined in your timetrex.ini.php file, and if so what is it set to?

Have you tried switching the timezone set in your employees preferences to "UTC" rather than "GMT" to see if that makes any difference?

[Yajasi]

Please review entire topic history for clarification.

[Yajasi]

According to the log, the default system timezone in use is: Asia/Jayapura

I'm glad you found that to be true. As you can see from my earlier post, I was thinking the same thing.
http://forums.timetrex.com/viewtopic.ph ... 851#p18816

Then the employees timezone is set to GMT.

Yes that's good it matches what I mentioned in my original post.
http://forums.timetrex.com/viewtopic.ph ... 851#p18543

Do you have system_timezone defined in your timetrex.ini.php file, and if so what is it set to?

Yes, I have tried both ways and detailed the results in an earlier post.
http://forums.timetrex.com/viewtopic.ph ... 851#p18839

Have you tried switching the timezone set in your employees preferences to "UTC" rather than "GMT" to see if that makes any difference?

I changed the employee preferences to UTC and am still having the same problems as mentioned in the previous posts. I also tried setting the system_timezone value to UTC and had all the same problems. I don't understand why the Accrual Used has a Different Date than the TimeSheet. TimeTrex handles the punches just fine with our setup, so why is there so much trouble with Accruals?

[shaunw]

Could you sign-up for a Cloud Community Edition instance and see if you can replicate the issue there?
https://www.timetrex.com/cloud_community.php

[Yajasi]

PROBLEM SOLVED

After some testing, we found that setting the timezone in the timetrex.ini.php file did not correct our problem, but I guess that's a bit obvious if you are reading the entire post. We left the setting active even though it did not correct the problem.

In actuality, we found that the timezone of our PostgreSQL database needed to be changed. We set the timezone in the PostgreSQL database to 'UTC' and now all of our Accural Dates match up with the day shown on the TimeSheet.

For others who are having this problem, we editing the following file (which may be different depending on your server setup):
/etc/postgresql/9.1/main/postgresql.conf
The timezone parameter can be found in the CLIENT CONNECTION DEFAULTS section of the file. We set our timezone to be:

Code: Select all

timezone = 'UTC'

TimeTrex is a great product! Thanks for all the help!

[shaunw]

I doubt that is the root cause of the problem, as the timezone the database is set too shouldn't matter because TimeTrex converts it to whatever timezone it requires.

The more likely cause is either the date format or the timezone you are in is ambiguous.

In the PostgreSQL console, if you run the following command:

Code: Select all

show datastyle;

Then run this:

Code: Select all

select now();

What is the output of both of those commands?

[Yajasi]

What is the output of both of those commands?

We get the following:

Code: Select all

postgres=# show datastyle;
ERROR:  unrecognized configuration parameter "datastyle"
postgres=# select now();
              now
-------------------------------
 2014-02-14 03:12:38.442148+00
(1 row)

The time show is in UTC since we set the timezone='UTC' in the postgres configuration file, and it is the correct time in UTC given that our server time is UTC+09 and the local time was 12:12pm when I ran the commands.

[shaunw]

Sorry, I had a typo, should be:

Code: Select all

show datestyle;

[Yajasi]

Oops I should have caught that. Here you go ...

Code: Select all

postgres=# show datestyle;
 DateStyle
-----------
 ISO, MDY
(1 row)

postgres=# select now();
              now
-------------------------------
 2014-02-17 00:25:32.906846+00
(1 row)

[shaunw]

Seems normal... Not sure what the root cause would be, but glad that the issue is resolved for you, even if the solution doesn't make sense to me.