TO_TIMESTAMP in Postgresql dialect differs from definition

[bas-info-nl]

I have written a nice query in Postgresql that converts a number of seconds into a timestamp with timezone, for example something like this:

SELECT TO_TIMESTAMP(1284352323);

This should result in a value of 2010-09-13 04:32:03+00.

Unfortunately the definition within the Postgresql Dialect, here h2/src/main/org/h2/mode/FunctionsPostgreSQL.java, expects 2 parameters, and will convert the (optional) first using the format specified in the second parameter to parse it.

I can't find any documentation on the Postgresql site, where the TO_TIMESTAMP function ever worked that way. Do enlighten me if I have missed that.

This is what I can find on https://www.postgresql.org/docs/14/functions-datetime.html:

 to_timestamp ( double precision ) → timestamp with time zone
Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone
to_timestamp(1284352323) → 2010-09-13 04:32:03+00

Should I look at making a PR for this change?

Cheers, Bas de Wit

[katzyn]

It looks like PosgreSQL has two different functions with the same name. H2 currently implements that one:

https://www.postgresql.org/docs/current/functions-formatting.html

to_timestamp ( text, text ) → timestamp with time zone

Converts string to time stamp according to the given format. (See also to_timestamp(double precision) in Table 9.32.)

to_timestamp('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 00:00:00-05

---

Should I look at making a PR for this change?

You can use something like that in your application:

SELECT TIMESTAMP WITH TIME ZONE '1970-01-01 00:00:00+00:00' + INTERVAL '1' SECOND * 1284352323;
-- or
SELECT TIMESTAMP WITH TIME ZONE '1970-01-01 00:00:00+00:00' + INTERVAL '1284352323' SECOND(10);

Unlike TO_TIMESTAMP(double precision), these expressions use only the syntax from the SQL Standard and they are supported by various DBMS (SELECT without FROM is not compliant with the Standard, only expressions are).

But if this is not an option, you can add alternative 1-argument version to FunctionsPostgreSQL. Don't forget to add test cases to this (or some else) file: https://github.com/h2database/h2database/blob/08a5856fd638407ecf3af8f19b6e8aac8d363eb6/h2/src/test/org/h2/test/scripts/compatibility/compatibility.sql#L704-L705

[bas-info-nl]

Thanks for the quick response Evgenij

So, I didn't see the formatting function variant of to_timestamp, thanks for pointing that out.

And the SELECT without FROM sample query I posted, is just that, a sample; it's not what I use in my application code :)

Anyway, for simplicity sake, I think I'll update the query to be SQL Standard compliant. And I'll have a look at a PR when I have time :)