Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sql: Add Data Type Formatting Functions #3781

Open
3 of 10 tasks
Tracked by #25469
nvanbenschoten opened this issue Jan 12, 2016 · 23 comments
Open
3 of 10 tasks
Tracked by #25469

sql: Add Data Type Formatting Functions #3781

nvanbenschoten opened this issue Jan 12, 2016 · 23 comments
Labels
A-sql-builtins SQL built-in functions and semantics thereof. A-sql-pgcompat Semantic compatibility with PostgreSQL A-sql-semantics A-tools-aws-dms Blocking support for AWS Database Migration Service C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) meta-issue Contains a list of several other issues. sync-me-8 T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)

Comments

@nvanbenschoten
Copy link
Member

nvanbenschoten commented Jan 12, 2016

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.

gz#10829

Jira issue: CRDB-6199

@nvanbenschoten nvanbenschoten added help wanted Help is requested / needed by the one who filed the issue to fix it. SQL labels Jan 12, 2016
@JackKrupansky
Copy link

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

@nvanbenschoten nvanbenschoten added the E-easy Easy issue to tackle, requires little or no CockroachDB experience label Feb 10, 2016
@petermattis petermattis added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) and removed SQL labels Feb 13, 2016
@petermattis petermattis modified the milestone: Beta Feb 14, 2016
@petermattis petermattis modified the milestones: 1.0, Beta Feb 21, 2016
@jkozlowski
Copy link

  • 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
Copy link

I think I'll give it a try

@spencerkimball spencerkimball modified the milestones: Later, 1.0 Mar 28, 2017
@spencerkimball
Copy link
Member

@mjibson could you prioritize this issue?

@maddyblue maddyblue self-assigned this Mar 30, 2017
@knz knz added the A-sql-pgcompat Semantic compatibility with PostgreSQL label Apr 28, 2018
@knz knz removed E-easy Easy issue to tackle, requires little or no CockroachDB experience help wanted Help is requested / needed by the one who filed the issue to fix it. labels Apr 28, 2018
@knz knz changed the title sql/parser: Add Data Type Formatting Functions sql: Add Data Type Formatting Functions Apr 28, 2018
@knz knz added A-sql-builtins SQL built-in functions and semantics thereof. meta-issue Contains a list of several other issues. labels Apr 28, 2018
@MEschenbacher
Copy link

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
Copy link

insaner commented Oct 20, 2020

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
Copy link
Collaborator

rafiss commented Oct 21, 2020

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

@rafiss rafiss added the T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) label May 12, 2021
@bangeneticalgorithms
Copy link

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
Copy link

Add format() to the list.

@lucacri
Copy link

lucacri commented Jun 28, 2021

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
Copy link

korpa commented Jul 21, 2021

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
Copy link

any news ?

@korpa
Copy link

korpa commented Jun 9, 2022

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

@intern7777
Copy link

intern7777 commented Aug 9, 2022

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

Screenshot

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

@otan otan added the A-tools-aws-dms Blocking support for AWS Database Migration Service label Oct 21, 2022
@otan
Copy link
Contributor

otan commented Oct 21, 2022

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
Copy link
Collaborator

rafiss commented Nov 8, 2022

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

craig bot pushed a commit that referenced this issue May 10, 2023
102787: builtins: implement to_char(date, string) r=rafiss a=otan

Release note (sql change): Introduce the `to_char(date, format)` builtin variant, which converts a given date to a string using the given format string.

Informs #3781

(if we had implicit casts this wouldnt be an issue!)

Co-authored-by: Oliver Tan <otan@cockroachlabs.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
A-sql-builtins SQL built-in functions and semantics thereof. A-sql-pgcompat Semantic compatibility with PostgreSQL A-sql-semantics A-tools-aws-dms Blocking support for AWS Database Migration Service C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) meta-issue Contains a list of several other issues. sync-me-8 T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)
Projects
None yet
Development

No branches or pull requests