public inbox for [email protected]
help / color / mirror / Atom feedIs `DATE` a function?
5+ messages / 4 participants
[nested] [flat]
* Is `DATE` a function?
@ 2023-10-07 13:01 jinser <[email protected]>
2023-10-07 14:28 ` Re: Is `DATE` a function? Bzzzz <[email protected]>
2023-10-07 14:38 ` Re: Is `DATE` a function? Tom Lane <[email protected]>
2023-10-16 15:42 ` Re: Is `DATE` a function? Cory Albrecht <[email protected]>
0 siblings, 3 replies; 5+ messages in thread
From: jinser @ 2023-10-07 13:01 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is `DATE` a function?
2023-10-07 13:01 Is `DATE` a function? jinser <[email protected]>
@ 2023-10-07 14:28 ` Bzzzz <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Bzzzz @ 2023-10-07 14:28 UTC (permalink / raw)
To: [email protected]
On Sat, 7 Oct 2023 21:01:59 +0800
jinser <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is `DATE` a function?
2023-10-07 13:01 Is `DATE` a function? jinser <[email protected]>
@ 2023-10-07 14:38 ` Tom Lane <[email protected]>
2023-10-07 16:18 ` Re: Is `DATE` a function? jinser <[email protected]>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-10-07 14:38 UTC (permalink / raw)
To: jinser <[email protected]>; +Cc: [email protected]
jinser <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is `DATE` a function?
2023-10-07 13:01 Is `DATE` a function? jinser <[email protected]>
2023-10-07 14:38 ` Re: Is `DATE` a function? Tom Lane <[email protected]>
@ 2023-10-07 16:18 ` jinser <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: jinser @ 2023-10-07 16:18 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: jinser <[email protected]>; [email protected]
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 <[email protected]> 于2023年10月7日周六 22:38写道：
>
> jinser <[email protected]> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is `DATE` a function?
2023-10-07 13:01 Is `DATE` a function? jinser <[email protected]>
@ 2023-10-16 15:42 ` Cory Albrecht <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Cory Albrecht @ 2023-10-16 15:42 UTC (permalink / raw)
To: jinser <[email protected]>; +Cc: [email protected]
Rather than `SELECT DATE();` you likely want `SELECT NOW();`
On Sat, Oct 7, 2023, 09:20 jinser <[email protected]> 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.
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-10-16 15:42 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-07 13:01 Is `DATE` a function? jinser <[email protected]>
2023-10-07 14:28 ` Bzzzz <[email protected]>
2023-10-07 14:38 ` Tom Lane <[email protected]>
2023-10-07 16:18 ` jinser <[email protected]>
2023-10-16 15:42 ` Cory Albrecht <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox