Kysely and 'default datetime'

[Wouter Hendriks]

When querying this database row:

+----------------+
|ANSWERED_AT     |
+----------------+
|DEFAULT DATETIME|
+----------------+

with Kysely:

await db<WvuawDB>()
    .selectFrom("wvuaw.aircall_calls")
    .select(["answered_at"])
    .execute();

it gets returned as:

{ answered_at: 0000-12-31T00:00:00.000Z }

Not really sure if this is Kysely's default behaviour or if something goes wrong in the underlying driver, but I kinda expected to get `null` as returned value.

[Arnold Hendriks]

It should have been null, but there were issues in the past properly storing these fields. https://gitlab.com/webhare/platform/-/commits/9738b0f5f640e62ef0ce803de688d3af0db66fd5 fixed those but you may have to rewrite the default datetimes to the database to really get nulls

[Wouter Hendriks]

What do you mean by "you may have to rewrite the default datetimes to the database"?

[Arnold Hendriks]

I believe the default datetimes were not stored properly as nulls and that that commit fixes the code, but it won't fix any data already stored

[Wouter Hendriks]

Just updated local WebHare, but still seems to be a problem.

Insert from TS:

    const newAircallCall = await db<WvuawDB>()
      .insertInto("wvuaw.aircall_calls")
      .values({
        started_at: this.convertUnixTimestampToDateTime(
          this.webhookData.data.started_at
        ),

      })
      .returning(["id"])
      .executeTakeFirst();

Database:

+----+--------------------+----------------+
|ID  |STARTED_AT          |ENDED_AT        |
+----+--------------------+----------------+
|1158|2024-05-23T10:12:58Z|DEFAULT DATETIME|
+----+--------------------+----------------+

Query in TS:

    const call = await db<WvuawDB>()
      .selectFrom("wvuaw.aircall_calls")
      .where("callid", "=", String(callId))
      .select(["id", "ended_at"])
      .executeTakeFirst();
    console.log(call);

Result in console.log:

[remote:log] { id: 1158, ended_at: 0000-12-31T00:00:00.000Z }

May have to do with the fact that "ended_at" is not present in the insertInto.values?

Setting ended_at to null explicitly results in:

{"@timestamp":"2024-05-23T10:18:37.462Z","message":"2024-05-23 12:18:37.462 CEST [43684] ERROR:  null value in column \"ended_at\" of relation \"aircall_calls\" violates not-null constraint","service":"platform:database","at":391565,"stream":"stdout"}

[Arnold Hendriks]

not related but curious - how is convertUnixTimestampToDateTime defined?  given that name how is is different from new Date(<unix timestamp>) ?

what does  

wh psql -c 'select * from wvuaw.aircall_calls where id = 1158' 

return ?

[Wouter Hendriks]

convertUnixTimestampToDateTime is pretty simple, basically just to convert secs to msecs

  private convertUnixTimestampToDateTime(unixTimestampSeconds: number) {
    return new Date(unixTimestampSeconds * 1000);
  }

psql command (id has changed):

SET
  id  |  callid   | outbound |     started_at      |              brokerguid              |   numberdigits   |   raw_digits   | missed_call_reason | hangup_cause | answered_at | ended_at
------+-----------+----------+---------------------+--------------------------------------+------------------+----------------+--------------------+--------------+-------------+-----------
 1414 | 472037911 | t        | 2024-02-05 11:39:36 | wrd:A4F7E0E38F254E4EB4B7D140542B7C35 | +41 22 518 05 10 | +31 6 30415725 |                    |              | -infinity   | -infinity

[Arnold Hendriks]

> May have to do with the fact that "ended_at" is not present in the insertInto.values?

what is the generated table interface? eg the WTS module has this:

export interface WebHareTestsuite_ConsilioIndex {

id: IsGenerated<number>;

groupid: string;

objectid: string;

text: string;

adate: Date;

grouprequiredindexdate: Date;

objectrequiredindexdate: Date;

indexdate: Date;

extradata: string;

}

as far as I know it should never generate "Date | null" at this moment, precisely because the database currently doesn't take a 'null's. So your insertInto should have generated an error ?

If I try it myself:

const baserec = { groupid: "", objectid: "", grouprequiredindexdate: defaultDateTime, objectrequiredindexdate: maxDateTime, indexdate: new Date, extradata: "" };

await db<WebHareTestsuiteDB>().insertInto("webhare_testsuite.consilio_index").values({ ...baserec, text: "row_defaultdate" }).execute();

No overload matches this call.
  Overload 1 of 2, '(row: InsertObject<WebHareTestsuiteDB, "webhare_testsuite.consilio_index">): InsertQueryBuilder<WebHareTestsuiteDB, "webhare_testsuite.consilio_index", InsertResult>', gave the following error.
    Argument of type '{ text: string; groupid: string; objectid: string; grouprequiredindexdate: Date; objectrequiredindexdate: Date; indexdate: Date; extradata: string; }' is not assignable to parameter of type 'InsertObject<WebHareTestsuiteDB, "webhare_testsuite.consilio_index">'.

Using wh psql:

webhare=# \d webhare_testsuite.consilio_index
                                                  Table "webhare_testsuite.consilio_index"
         Column          |            Type             | Collation | Nullable |                           Default                          
-------------------------+-----------------------------+-----------+----------+-------------------------------------------------------------
 id                      | integer                     |           | not null | (webhare_testsuite.webhare_autonrs_consilio_index_id(1))[1]
 adate                   | timestamp without time zone |           | not null | '-infinity'::timestamp without time zone
 groupid                 | character varying(1024)     |           | not null | ''::text
 grouprequiredindexdate  | timestamp without time zone |           | not null | '-infinity'::timestamp without time zone
 indexdate               | timestamp without time zone |           | not null | '-infinity'::timestamp without time zone
 objectid                | character varying(1024)     |           | not null | ''::text
 objectrequiredindexdate | timestamp without time zone |           | not null | '-infinity'::timestamp without time zone
 text                    | character varying(1024)     |           | not null | ''::text
 extradata               | character varying(4096)     |           | not null | ''::text

-infinity === DEFAULT DATETIME (the *harescript* default if the column is missing) and not null

In the WRD API and WHFS APIs we're making DEFAULT DATETIME/MAX_DATETIME and 'null' the same thing where we can, as that makes the most sense conceptually. I think we may need to do that for 'raw' WHDB too... but we haven't bothered pinning that down too much yet.

[Wouter Hendriks]

Yeah, I was @ts-ignoring the overload error :-)

 As per your example, if I want to set dates to "default" initially without causing overload errors, I should use "defaultDateTime"? And the best way to check "if datetime col is set to any value other than default" would be to use `row.date === defaultDateTime`? At least for now?

[Arnold Hendriks]

On Thursday, August 22, 2024 at 3:42:10 PM UTC+2 Wouter Hendriks wrote:

Yeah, I was @ts-ignoring the overload error :-)

Okay, don't do that when asking why things break :) 

 As per your example, if I want to set dates to "default" initially without causing overload errors, I should use "defaultDateTime"? And the best way to check "if datetime col is set to any value other than default" would be to use `row.date === defaultDateTime`? At least for now?

For now that seems best. .

The problem is that in the transition from dbserver to postgresql, we 'hid' a lot of inconsistencies in the HareScript driver - eg the fact that dbserver and HareScript do not consider NULL to be special, but postgresql does (and other databases do too, but there are plenty of differences even between them). 

From JavaScript you're not using the HareScript database drivers, and when accessing the tables directly (ie not through @webhare/whfs or @webhare/wrd where we can hide these things for you) you will see what we actually put in the database. And then you see the implementation details that weren't visible from HareScript, eg that defaultDatetime is not the same as null in the database.