Skip to content

The return type of EXTRACT #1992

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

Open
Tracked by #8282 ...
liukun4515 opened this issue Mar 11, 2022 · 9 comments
Open
Tracked by #8282 ...

The return type of EXTRACT #1992

liukun4515 opened this issue Mar 11, 2022 · 9 comments

Comments

@liukun4515
Copy link
Contributor

I think the result type of u32 is not enough.
From the spark,

spark-sql> SELECT EXTRACT(YEAR FROM TIMESTAMP '-2000-12-16 12:21:13');
-2000

But this is not related to this issue, I will open a follow up pull request and discuss the returned type.

Originally posted by @liukun4515 in #1991 (comment)

@Ted-Jiang
Copy link
Member

Do we need support negative timestamp or date?

@Ted-Jiang
Copy link
Member

PG not support negative timestamp

postgres@33a4a5d969c0:/$ psql
psql (13.5 (Debian 13.5-1.pgdg110+1))
Type "help" for help.

postgres=# SELECT EXTRACT(YEAR  FROM TIMESTAMP '-2001-02-16 20:38:40');
ERROR:  time zone displacement out of range: "-2001-02-16 20:38:40"
LINE 1: SELECT EXTRACT(YEAR  FROM TIMESTAMP '-2001-02-16 20:38:40');

@xudong963
Copy link
Member

I don't think we need to support negative timestamp.

@liukun4515
Copy link
Contributor Author

because i hit this document from PG https://www.postgresql.org/docs/14/functions-datetime.html and it points out that
"The extract function returns values of type numeric. The following are valid field names"

other point from the definition

extract ( field from timestamp ) → numeric

Get timestamp subfield; see [Section 9.9.1](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)

extract(hour from timestamp '2001-02-16 20:38:40') → 20

@liukun4515
Copy link
Contributor Author

now we can leave this issue and keep discussion it.

@waitingkuo
Copy link
Contributor

@liukun4515 should we change the return to decimal/numeric type?

@waitingkuo
Copy link
Contributor

just found that date_part has different return type in pg

date_part ( text, timestamp ) → double precision

    Get timestamp subfield (equivalent to extract); see [Section 9.9.1](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)
    date_part('hour', timestamp '2001-02-16 20:38:40') → 20

@waitingkuo
Copy link
Contributor

from postgresql's doc
https://www.postgresql.org/docs/current/functions-datetime.html

The extract function is primarily intended for computational processing. For formatting date/time values for display, see Section 9.8.

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:

date_part('field', source)
Note that here the field parameter needs to be a string value, not a name. The valid field names for date_part are the same as for extract. For historical reasons, the date_part function returns values of type double precision. This can result in a loss of precision in certain uses. Using extract is recommended instead.

@jhorstmann
Copy link
Contributor

PG not support negative timestamp

This sounded surprising, it seems what is not supported is the input syntax starting with a minus sign. Negative timestamps are supported, just with a different syntax:

SELECT '2022-10-01 BC'::timestamp;
-002021-10-01T00:00:00.000Z
SELECT EXTRACT(YEAR FROM '2022-10-01 BC'::timestamp);
-2022

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

5 participants