Cockroach: 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)
• [ ] 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)

[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?

[tomholub]

These are useful. Is there currently any way to convert a timestamp to a string?

Legacy sql snippet: to_char(timestamp, 'YYYY-MM') as month

unknown function: to_char()

[mjibson]

We have a experimental_strftime function that can do that: https://www.cockroachlabs.com/docs/stable/functions-and-operators.html#date-and-time-functions

[tomholub]

Perfect, thanks! Works great. Thanks for the swift response.

select experimental_strftime(now(), '%Y-%m');

[awoods187]

This is commonly use with INTERVAL (http://www.postgresqltutorial.com/postgresql-interval/)

[jordanlewis]

This is so unspecific as to be unactionable.

[rafiss]

Re-opening, because we still could add these functions. I added a checklist in the original issue description.

[awoods187]

I ran into this today when doing a recursive demo:

WITH RECURSIVE rec AS (
        SELECT id, 0 AS degree
        FROM employees
        WHERE id = 1 -- John, the CEO
    UNION ALL
        SELECT e.id, rec.degree + 1 AS degree
        FROM employees e
        JOIN rec ON e.manager_id = rec.id
)
SELECT
    'John and ' || employees.name || ' are separated by ' ||
    TO_CHAR(rec.degree, '9') ||
    ' degrees of separation'
FROM employees
JOIN rec
    ON (employees.id = rec.id)
    WHERE employees.name = 'George';

[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.)