Author Topic: unable to change date - stays 1 jan 1970 (Read 4288 times)

wimvanaelst · January 25, 2011, 10:55:15 am

Dear all,
I used a profile to update a custom date field for many contacts at once. I made some changes first, then clicked back, and made the rest of the changes. Maybe not the way to do it, but I hoped to change time.
Now it seems that some date fields updated correctly, but some date fields are now permanently set to 1 jan 1970. Whatever I try to do (clear the date, using a profile, not using a profile) to change the date... the date stays 1 jan 1970.
It's unfortunate since half of the members have now a correct date, and the other half this date of 1 jan 1970.
Can anybody give me a suggestion about how to solve this ? I'm not a database guru.

Erik Hommel · January 25, 2011, 10:59:54 am

Hello Wim,
If I remember correctly 1 jan 1970 is the value most applications will give you if the field in the MySQL database is set to null? So you probably need to change the date to "" or something like "0000-00-00 00:00:00". If you browse on MySQL and 1 jan 1970 you should be able to find it.
Erik

wimvanaelst · January 25, 2011, 03:24:52 pm

Thanks Erik,
I found the records in the SQL dB, and deleted them all. However, once I try to change the date again in civicrm, the date of 1 jan 1970 keeps reappearing.
It seems civicrm is not accepting the changes, and some 'out of range' error sets the date to 1 jan 1970 again and again.

I also noticed that the years of some of the dates which first seemed correct have changed (28 july 2009 has become 28 july 2011).
It seems there might be something wrong with the date picker.

wimvanaelst · January 25, 2011, 04:12:22 pm

I updated civicrm from 3.3.2 to 3.3.3, but unfortunately this didn't solve the problem.
That custom date field stays 1 januari 1970, 1:00.
I am not able to change it.

Erik Hommel · January 25, 2011, 10:54:58 pm

Updating won't help, the problem is not in CiviCRM. 1 jan 1970 is a representation of the NULL value in MySQL, that is not depending on CiviCRM. You need to replace the value NULL in the database, so do something like:
"UPDATE <table name> SET <datefield> = '' WHERE ISNULL(<datefield>)

wimvanaelst · January 26, 2011, 09:53:55 am

Thanks for your help Erik.

I executed the suggested command: UPDATE civicrm_value_naturopathica_2 SET datum_ondertekening_gedragscode__57 = '' WHERE ISNULL(datum_ondertekening_gedragscode__57)

But even after that, when I go to civicrm and I try to update a date, after submitting a date in such an 'empty' field it is again reset to 1 jan 1970.
I also tried to change the date of an existing field to 26 july 2009, and after submitting it showed as 26 july 2011.
I did select first the year, then the month and then the date in the date picker.

I did notice the field type in SQL is not a 'date' field as I expected, but a 'datetime' field.
In Drupal the same custom field shows as a 'date' data type. I'm not sure if this is relevant.

Erik Hommel · January 26, 2011, 10:28:47 am

No, the distinction between date and datetime does not play a role here....but the behaviour of your dates is weird! Have you tried the changing the dates on the demo CiviCRM site? If the same behaviour occurs there on empty date fields you should raise an issue (bug) but I am fairly sure it performs OK there (or I would imagine we would have seen posts about it earlier).
It seems like have an unfortunate local problem....my suggestions are finished without looking at your local installation. All I have left is this: do attend for the CiviCRM developer camp in Brussels on 3 and 4 feb? (I am assuming from your name that you are either Dutch or Flemish but I might be totally wrong......) Then we can have a look at the problem too :-)

wimvanaelst · February 01, 2011, 01:44:24 pm

Hi Erik,
Yes I'm dutch and from Belgium, but my organization cannot afford the fee, and it's also a bit short notice for me to take a day off at work. I'm just a volunteer.
Unfortunately I have to get the whole administration of the organization fixed in only a few weeks. I was hoping to put certain dates in the system, but it seems that at least some date fields increase the year with one year too. Great.....

For example: I type 26/11/2010 but it's registered as 26/11/2011, even if I do select first the year.
I cannot remember having seen this problem before, but now I hope I don't have to check all dates in the system.

For now I have no solution to do everything by hand untill I find some hours (at night?) free time.
Thanks however for your suggestions.

Erik Hommel · February 01, 2011, 11:41:37 pm

Hi Wim,
there is something not right with your specific site / installation, what you mention with dates is not standard behaviour. I would strongly recommend getting someone to look at it because it might also bite you in other areas. Good luck!

wimvanaelst · February 02, 2011, 03:52:58 am

Erik,
Ik have contacted our provider, and they informed me that they moved the database a few months ago to another machine (power supply problem).
I hope it has something to do with a misconfiguration of date/time settings from their part.
But even then I guess I would not be the only one with these kind of problems.
I'll keep you informed....

Erik Hommel · February 02, 2011, 03:57:23 am

Please do, I have not encountered the problems in the CiviCRM projects I am involved with. Have you tried to replicate the problem on the CiviCRM demo site?
Erik

wimvanaelst · February 02, 2011, 04:23:37 am

Hi Erik,

Good news ! I found the solution. I noticed that the problem only presented itself with self-defined date values, not with standard date fields, so I investigated the 'date representation' of my self-defined fields.
By changing the 'date representation' from (d MM yyyy) to the site default (dd/mm/yyyy) the problem went away. My conclusion is that the date representation of date/time values is not independent between fields, but unique for a site.
I'm confused why the choice of different date representations for the date field in self-defined date values is offered anyway.
Is this worth describing in a bug report or is this a 'feature' ? (yes... we use Microsoft at work

)

Erik Hommel · February 02, 2011, 04:50:20 am

The fact that you can define different date representation for a date field I would certainly call a feature.....the difference with CiviCRM is that there is no one with authority to claim that it is

but you can certainly do a "in my opinion" or in short IMO. If I have a site for a Dutch customer I want to be able to change the representation of the dates and the flexibility of the custom data in general is a big plus. So I would not report it as a bug, but feel free if you think it needs fixing

Erik

chrissncco · February 02, 2011, 05:49:07 am

Wim,

I'm not sure where you have *not* configured this but the date 1 Jan 1970 is the date that php defaults to when it is not configured. This is the Unix Epoch. hxxp://www.php.net/manual/en/function.time.php

Perhaps there's a php configuration that you've overlooked?

C.

wimvanaelst · February 06, 2011, 12:51:57 pm

I cannot change the php config, but I have asked the provider to look into it.

CiviCRM Community Forums (archive)

News:

Author Topic: unable to change date - stays 1 jan 1970 (Read 4288 times)

wimvanaelst

unable to change date - stays 1 jan 1970

Erik Hommel

Re: unable to change date - stays 1 jan 1970

wimvanaelst

Re: unable to change date - stays 1 jan 1970

wimvanaelst

Re: unable to change date - stays 1 jan 1970, 1:00

Erik Hommel

Re: unable to change date - stays 1 jan 1970

wimvanaelst

Re: unable to change date - stays 1 jan 1970

Erik Hommel

Re: unable to change date - stays 1 jan 1970

wimvanaelst

Re: unable to change date - stays 1 jan 1970

Erik Hommel

Re: unable to change date - stays 1 jan 1970

wimvanaelst

Re: unable to change date - stays 1 jan 1970

Erik Hommel

Re: unable to change date - stays 1 jan 1970

wimvanaelst

Re: unable to change date - stays 1 jan 1970

Erik Hommel

Re: unable to change date - stays 1 jan 1970

chrissncco

Re: unable to change date - stays 1 jan 1970

wimvanaelst

Re: unable to change date - stays 1 jan 1970