ERROR: function to_timestamp(unknown) does not exist

[cyrille-arundo]

  ERROR: function to_timestamp(unknown) does not exist
    HINT: 🔨 Please note that pg-mem implements very few native functions.

                👉 You can specify the functions you would like to use via "db.public.registerFunction(...)"

    🐜 This seems to be an execution error, which means that your request syntax seems okay,
    but the resulting statement cannot be executed → Probably not a pg-mem error.

I have added the function as follow:

db.public.registerFunction({
    name: 'TO_TIMESTAMP'.toLowerCase(),
    args: [DataType.integer],
    returns: DataType.timestamp,
    implementation: t => moment.unix(t),
});

This works very well but it is limited to the following case SELECT TO_TIMESTAMP(1612954645); => 2021-02-10T10:57:25.000Z. The given input is an unix time (number).

The PostgreSQL specification lists many more cases where the input is string: https://www.postgresqltutorial.com/postgresql-to_timestamp/

It would be nice to have the TO_TIMESTAMP implemented in pg-mem

[oguimbal]

Hi !

You could declare multiple overloads of the same function like below.

As a side note: Beware, it could work to return a moment instance because pg-mem also uses moment internally, but that might change, so prefer returning dates.

db.public.registerFunction({
    name: 'to_timestamp',
    args: [DataType.integer],
    returns: DataType.timestamp,
    implementation: t => moment.unix(t).toDate(),
});

db.public.registerFunction({
    name: 'to_timestamp',
    args: [DataType.text, DataType.text],
    returns: DataType.timestamp,
    implementation: (t, f) => moment.utc(t, f).toDate(),
});

That said, that will only work for formats that would expect a moment format as input, which is substentially different from a postgres one. I did that for to_date(), but I'm have regrets now (pg-mem does not behave like pg on this)

So you could just implement the formats that you use.

If you choose to implement a thourough version of to_timestamp that is compatible with the pg one, I would gladly include it in pg-mem implementation.

Otherwise, I'll do that one day to fix to_date :)

FYI, all date/timestamp functions shipped with pg-mem are or will be here