0

Today I found that many of my production databases are having a future date for the column named status_update, which sets ONLY by now() function.

Most of the dates are for 8 October:

  • 2014-10-08 08:20:42.97564
  • 2014-10-08 16:31:30.304743
  • etc.

But some of them:

  • 2014-10-09 08:31:44.775589
  • 2014-10-10 08:50:24.235533
  • 2014-10-13 10:42:20.038917
  • etc.

Please can anyone help me with this issue? How could it be?

My application is built on PHP. Current time is 2014-10-07 17:35:00.

SELECT now() returns 2014-10-07 17:35:56.192627+01

Thanks in advance.

UPDATE

My fault, I missed pjobdate + current_time expression that used for setting the value for status_update. Sorry.

alex23
  • 353
  • 2
  • 13
  • 1
    `now()` does not set any column on any table. Please show us your logic (f.ex. your trigger), which updates that column. – pozs Oct 08 '14 at 12:18
  • hi @pozs, thanks to you I rechecked all functions and triggers and found this expression `pjobdate + current_time` for setting value for status_update. I surprised as I thought I learned the project. – alex23 Oct 09 '14 at 13:20

1 Answers1

0

Probably a timezone issue see: Local time zone offset in PostgreSQL

and try

 SELECT  current_setting('TIMEZONE');

This is probably true for the 2014-10-08 dates, the ones way off in the future are probably from the date getting set somewhere else. I would take a look at ALL locations where that column gets updated, triggers, procedures, PHP code, etc

Community
  • 1
  • 1
Dan
  • 876
  • 5
  • 15
  • Thanks for your answer. All databases have 'Europe/London' timezone as it should be. Yes, I've checked all locations as you adviced. The project is pretty big, probably i've missed something, I'll let you know when find anything. – alex23 Oct 07 '14 at 16:57