> > Concerning #33
> >
> > then you come back on the last solution with :
> > $QUERY = "SELECT cc_did.id, cc_did_destination.id, billingtype,
> > tariff, destination, voip_call, username".
> > " FROM cc_did, cc_did_destination, cc_card ".
> > " WHERE id_cc_did=cc_did.id and
> > cc_card.id=id_cc_card and cc_did_destination.activated=1 and
> > cc_did.activated=1 and did='$mydnid' ".
> > - " AND cc_did.startingdate<= CURRENT_TIMESTAMP
> > AND (cc_did.expirationdate > CURRENT_TIMESTAMP OR
> > cc_did.expirationdate IS NULL OR ".
> > - " cc_did.expirationdate = '0000-00-00
> > 00:00:00') ORDER BY priority ASC";
> > + " AND cc_did.startingdate<= CURRENT_TIMESTAMP
> > AND (cc_did.expirationdate > CURRENT_TIMESTAMP OR
> > cc_did.expirationdate IS NULL";
> > + if ($A2B->config["database"]['dbtype'] != "postgres"){
> > + // MYSQL
> > + $QUERY .= " OR cc_did.expirationdate =
> > '0000-00-00 00:00:00'";
> > + }
> > + $QUERY .= ") ORDER BY priority ASC";
> >
> >
> >
> > can we clear this
> >
> >
> That last patch simply removes the check that causes the AGI to fail on
> DIDs, and works well for me here.
>
http://trac.asterisk2billing.org/cgi-bi ... dates.diff>
> I was looking at all the instances in the code where "0000-00-00" is
> referenced and there are many. It seemed like I would like introduce
> more bugs trying to change all instances to a new different default
> date. Then I learned that MySQL substitutes any invalid date with
> '0000-00-00', whereas Postgres will refuse to do the insert/update.
>
> I'm not sure what the best long-term solution is for A2B to remain
> database agnostic with respect to these dates. Perhaps we can change
> the tests to be <'1980-01-01' or some other obviously bogus date, then
> each database can have it's own preferred default date easily. I know
> Postgresql evaluates '0001-01-01'<'1980-01-01'::timestamp to be true. I
> think if MySQL evaluates '0000-00-00'<'1980-01-01' to be true this may
> be the solution. I don't have a MySQL install handy to test this.
>
> Then I noticed '-infinity' in table 8-13 in section 8.5.1.5 Special
> values:
http://www.postgresql.org/docs/8.0/stat ... etime.html> Perhaps we could set a global variable NULL_TIMESTAMP based on the
> dbtype to either '0000-00-00' or '-infinity' and use it within all the
> queries. I might have a play with that tomorrow if I get some time.
>
> I also noticed in the Postgres schema some fields use 'timestamp', and
> others use 'timestamp without timezone'. Surely they should all be the
> same? So I want to fix that too, but I'm unsure whether to use 'with'
> or 'without timezone' on all columns. I think they should all have
> 'without timezone', yes?
> I think this would fix a couple of bugs I've seen: on non-UTC timezones
> immediately after creating a ratecard/callplan it won't work until
> $UTC_offset hours later.
creationdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
only one can be initiate at CURRENT_TIMESTAMP, if you have more than 1 you will get the following error :
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
So when postgresql will use a NULL Value mysql will replace it by 0000-00-00 00:00:00.
Well the point here also is that we should not to have any null or 0000 value on those field if they are correctly initiate, so this is more a security measure in order to avoid sql error (well the intent fails
maybe an other simple solution would be that we initiate the value to a usable date like,
creationdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
startingdate TIMESTAMP DEFAULT '2000-01-01 00:00:00',
expirationdate TIMESTAMP DEFAULT '2000-01-01 00:00:00',