sql: Add Data Type Formatting Functions

[nvanbenschoten]

Eventually we are going to want to add support for converting various data types to formatted strings, and from formatted strings back again. Postgres handles this well and provides a lot of detail on the specifics of its implementation here.

• to_char(timestamp, text) sql,builtins: support to_char formatting builtins for timestamp and interval #91156
• to_char(interval, text) sql,builtins: support to_char formatting builtins for timestamp and interval #91156
• to_char(date, text) builtins: implement to_char(date, string) #102787
• to_char(int, text)
• to_char(double precision, text)
• to_char(numeric, text)
• to_date(text, text)
• to_timestamp(text, text)
• to_timestamp(double precision) (builtins: implement to_timestamp #77591)
• to_number(text, text)

gz#10829

Jira issue: CRDB-6199

[JackKrupansky]

PG also has the concept of a pair of I/O Functions for each datatype, one function to parse and one to format. The pg_type table names that pair of I/O functions, as well as a pair of binary send/receive functions.

Ultimately, there are any number of format/parse or serialize/deserialize function pairs. For example, encoding of datum values for storage in the KV store (like the decimal type.)

[jkozlowski]

• to_char(timestamp, text)
• to_char(interval, text)
• to_char(int, text)
• to_char(double precision, text)
• to_char(numeric, text)
• to_date(text, text)
• to_timestamp(text, text)
• to_timestamp(double precision)
• to_number(text, text)

[vaskinyy]

I think I'll give it a try

[spencerkimball]

@mjibson could you prioritize this issue?

[MEschenbacher]

I came across the unavailability of postgres' to_timestamp while moving applications from postgresql to cockroachdb.

Is there a statement which replaces to_timestamp and is supported by both postgresql and cockroachdb?

select to_timestamp(1586233649);
select to_timestamp('1586233649');

[insaner]

Just ran into this as well. Shame that #7976 and #9018 were abandoned (4 years ago?). The needed function in my case was to_char().

I worked around it by using SELECT *, my_timestamp_col::TIMESTAMP::DATE as date_fmt FROM mytable.

As well, neither of experimental_strftime(my_timestamp_col, 'YYYY-MM-DD') or extract('date', my_timestamp_col) worked.

[rafiss]

CockroachDB does support int->timestamp cast, though it is an experimental feature. See https://www.cockroachlabs.com/docs/stable/int.html#supported-casting-and-conversion (Postgres does not support this, so it doesn't address the question of finding something that works for both DBs.)

[bangeneticalgorithms]

I was trying to get a cockroach based backend working with Apache Superset (a very popular visualization framework). But it fails to work on CockroachDB because to_timestamp() has not yet been implemented. It would be nice if this issue is addressed.

[LeviticusMB]

Add format() to the list.

[lucacri]

The to_char function is used in Laravel Nova in the aggregate queries. It generates the following

select to_char("users"."created_at" - interval '4 HOUR', 'YYYY-MM-DD') as date_result, count("users"."id") as aggregate from "users" where "users"."created_at" between 2021-06-22 00:00:00 and 2021-06-28 19:56:46 group by to_char("users"."created_at" - interval '4 HOUR', 'YYYY-MM-DD') order by "date_result" asc)

Implementing the time functions will definitely improve the compatibility with PostgreSQL, and make adoption easier

[korpa]

I was trying to get a cockroach based backend working with Apache Superset (a very popular visualization framework). But it fails to work on CockroachDB because to_timestamp() has not yet been implemented. It would be nice if this issue is addressed.

@bangeneticalgorithms Superset 1.0 works fine with CockroachDB. After Upgrading to v1.2 following issue appears: apache/superset#15825

[altanozlu]

any news ?

[korpa]

to_timestamp was developed: #82523
But will not released in 22.1.x #82523 (comment)

[intern7777]

I think to_char seems to be documented as if it is supported already. Am I reading it wrong?

https://www.cockroachlabs.com/docs/stable/functions-and-operators.html#date-and-time-functions

[otan]

I think to_char seems to be documented as if it is supported already.

the one org version works, yes.

---

to_char(timestamp[tz], string) is needed by AWS DMS during "validation" mode. cc @rafiss fyi if sql exp can prioritise :)

[rafiss]

Update: to_char(timestamp, text) and to_char(interval, text) (#91156) will be available in v22.2.1, coming out in December.