Thread: Is `DATE` a function?
Hello, Friends, Use it like a normal function: playground=# SELECT DATE(); ERROR: function date() does not exist LINE 1: SELECT DATE(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. playground=# SELECT DATE('2022-01-13'); date ------------ 2022-01-13 (1 row) Another reason I think this is a function is that other types don't seem to have the same behavior: playground=# SELECT integer('123'); ERROR: syntax error at or near "(" LINE 1: SELECT integer('123'); ^ The Table 9.33. Date/Time Functions in the documentation (https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-TABLE) doesn't mention this... If I missed it, where should I find the description? Or maybe it actually should be in this table? Thanks in advance.
On Sat, 7 Oct 2023 21:01:59 +0800 jinser <aimer@purejs.icu> wrote: Hi, > Hello, Friends, > > Use it like a normal function: > > playground=# SELECT DATE(); > ERROR: function date() does not exist > LINE 1: SELECT DATE(); > ^ > HINT: No function matches the given name and argument types. You > might need to add explicit type casts. > > playground=# SELECT DATE('2022-01-13'); > date > ------------ > 2022-01-13 > (1 row) > > Another reason I think this is a function is that other types don't > seem to have the same behavior: > > playground=# SELECT integer('123'); > ERROR: syntax error at or near "(" > LINE 1: SELECT integer('123'); > ^ > > The Table 9.33. Date/Time Functions in the documentation > (https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-TABLE) > doesn't mention this... > If I missed it, where should I find the description? Or maybe it > actually should be in this table? Is is both a function and an operator, but the function has an arity of one (text, representing a date, timestamp, etc), hence the error message you've got. If you just want to get today's date, use : SELECT current_date; or if you want to twist your mind : SELECT date(now()); Jean-Yves
jinser <aimer@purejs.icu> writes: > playground=# SELECT DATE('2022-01-13'); > date > ------------ > 2022-01-13 > (1 row) Sure, there are functions named date(): postgres=# \df date List of functions Schema | Name | Result data type | Argument data types | Type ------------+------+------------------+-----------------------------+------ pg_catalog | date | date | timestamp with time zone | func pg_catalog | date | date | timestamp without time zone | func (2 rows) The reason these aren't explicitly documented is that they are intended as implementation support for casts. postgres=# \dC date List of casts Source type | Target type | Function | Implicit? -----------------------------+-----------------------------+-------------+--------------- date | timestamp with time zone | timestamptz | yes date | timestamp without time zone | timestamp | yes timestamp with time zone | date | date | in assignment timestamp without time zone | date | date | in assignment (4 rows) Hence, the preferred spelling is more like select now()::date; or if you want to be SQL-spec-compatible, select cast(now() as date); but for historical reasons we like to let you also write select date(now()); which is managed (in most cases) by naming cast implementation functions the same as the target type. > Another reason I think this is a function is that other types don't > seem to have the same behavior: > playground=# SELECT integer('123'); > ERROR: syntax error at or near "(" You're running into a couple of things there: INTEGER is a reserved word, and the cast functions for that type are named after the internal type name "int4". postgres=# \dC integer List of casts Source type | Target type | Function | Implicit? ------------------+------------------+--------------------+--------------- "char" | integer | int4 | no bigint | integer | int4 | in assignment bit | integer | int4 | no boolean | integer | int4 | no double precision | integer | int4 | in assignment integer | "char" | char | no ... postgres=# select int4('123'); int4 ------ 123 (1 row) Note that none of these have anything to do with the syntax for a typed literal, which is "type-name quoted-literal" with no parentheses: postgres=# select date 'today'; date ------------ 2023-10-07 (1 row) postgres=# select integer '42'; int4 ------ 42 (1 row) Some aspects of the behavior might look the same, but there are a lot of edge cases. regards, tom lane
Thank you so much for your explanation. With the reminder of ”cast function“, I found a more detailed explanation in the document that I missed before: https://www.postgresql.org/docs/16/sql-expressions.html#SQL-SYNTAX-TYPE -CASTS. Thanks again everyone :) Tom Lane <tgl@sss.pgh.pa.us> 于2023年10月7日周六 22:38写道: > > jinser <aimer@purejs.icu> writes: > > playground=# SELECT DATE('2022-01-13'); > > date > > ------------ > > 2022-01-13 > > (1 row) > > Sure, there are functions named date(): > > postgres=# \df date > List of functions > Schema | Name | Result data type | Argument data types | Type > ------------+------+------------------+-----------------------------+------ > pg_catalog | date | date | timestamp with time zone | func > pg_catalog | date | date | timestamp without time zone | func > (2 rows) > > The reason these aren't explicitly documented is that they are intended as > implementation support for casts. > > postgres=# \dC date > List of casts > Source type | Target type | Function | Implicit? > -----------------------------+-----------------------------+-------------+--------------- > date | timestamp with time zone | timestamptz | yes > date | timestamp without time zone | timestamp | yes > timestamp with time zone | date | date | in assignment > timestamp without time zone | date | date | in assignment > (4 rows) > > Hence, the preferred spelling is more like > > select now()::date; > > or if you want to be SQL-spec-compatible, > > select cast(now() as date); > > but for historical reasons we like to let you also write > > select date(now()); > > which is managed (in most cases) by naming cast implementation > functions the same as the target type. > > > Another reason I think this is a function is that other types don't > > seem to have the same behavior: > > > playground=# SELECT integer('123'); > > ERROR: syntax error at or near "(" > > You're running into a couple of things there: INTEGER is a reserved > word, and the cast functions for that type are named after the > internal type name "int4". > > postgres=# \dC integer > List of casts > Source type | Target type | Function | Implicit? > ------------------+------------------+--------------------+--------------- > "char" | integer | int4 | no > bigint | integer | int4 | in assignment > bit | integer | int4 | no > boolean | integer | int4 | no > double precision | integer | int4 | in assignment > integer | "char" | char | no > ... > > postgres=# select int4('123'); > int4 > ------ > 123 > (1 row) > > Note that none of these have anything to do with the syntax for > a typed literal, which is "type-name quoted-literal" with no > parentheses: > > postgres=# select date 'today'; > date > ------------ > 2023-10-07 > (1 row) > > postgres=# select integer '42'; > int4 > ------ > 42 > (1 row) > > Some aspects of the behavior might look the same, but there > are a lot of edge cases. > > regards, tom lane >
Rather than `SELECT DATE();` you likely want `SELECT NOW();`
On Sat, Oct 7, 2023, 09:20 jinser <aimer@purejs.icu> wrote:
Hello, Friends,
Use it like a normal function:
playground=# SELECT DATE();
ERROR: function date() does not exist
LINE 1: SELECT DATE();
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
playground=# SELECT DATE('2022-01-13');
date
------------
2022-01-13
(1 row)
Another reason I think this is a function is that other types don't
seem to have the same behavior:
playground=# SELECT integer('123');
ERROR: syntax error at or near "("
LINE 1: SELECT integer('123');
^
The Table 9.33. Date/Time Functions in the documentation
(https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-TABLE)
doesn't mention this...
If I missed it, where should I find the description? Or maybe it
actually should be in this table?
Thanks in advance.