Set timestamp field to accept value 0000-00-00 00:00:00

Question

I’m in the wrong postgres, because a field that is timestamp this receiving value '0000-00-00 00:00:00', can tell me how to set up a field timestamp accept date and time reset ?

Follows error:

ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
  LINE 120: '0000-00-00 00:00:00',

Answer 1

Well - that’s not a valid timestamp, is it? There’s no year 0 in our calendar - it’s 1A.C and then year 1 D.C. - the date and time representations in this format don’t account for those dates.

The Postgresql documentation says that it should behave well with "before Christ" dates, but the string format for timestamps "YYYY-MM-DD HH:mm" does not allow this. I did not check the document to check if timestamp can be years "before Christ" or if this is limited to "date types".

Anyway, the smallest possible timestamp on postgres is January 1, 1 A.D., 00:00:00 - and you can enter this value into the bank using the string '-infinity' in your query. (But '0001-01-01 00:00:00' will also work).

Of course dates are strange when you pass some limits where there has been calendar change - so maybe the best thing is to build your application considering as "timestamp 0" a closer limit.

All Unix systems - including Linux and Macos, begin internal counting in 1970-1-1 , for example, and this may be a good date for you to consider as "0". Other systems consider "date 0", 1900-1-1 - is at the discretion of what makes more sense there.

What to do if you have no control over the incoming data

By your question, you imply that your bank is receiving these values and you have no way to change that.

Well, for "year zero does not exist in the Gregorian calendar", there is no way to configure Postgresql to accept 0000-00-00 as a valid date, no matter what you do. I believe that the way in this case is: create another column of type datetime, which will be the column with the date effectively, that your applications will consume and update. Transform the current column where the default timestamps arrive into a text column (char) - and create a Trigger to, for each insertion in this column, copy the value to the "real" column if it is a valid timestamp, or, if it is invalid (such as "0"), insert a default value (one of the above suggestions for "0"). This at least temporarily until whoever sends the data so you can correct on their side, and not send an invalid value.