postgresql - DATE_PART get no such function error

Question

tables:

create table product (
product_id int,
product_name varchar(50),
primary key (product_id));

insert into product values
(1, 'LC Phone'),
(2, 'LC T-Shirt'),
(3, 'LC Keychain');

create table sales (
product_id int,
period_start date,
period_end date,
average_daily_sales int);

insert into sales values
(1, '2019-01-25', '2019-02-28', 100),
(2, '2018-12-01', '2020-01-01', 10),
(3, '2019-12-01', '2020-01-31', 1);

Now I'd like to get the days between the period_end and period_start in table sales, I learned in postgresql I can use DATE_PART to calculate days different, so my sql is

select DATE_PART('day', period_end - period_start) as days from sales;

and I get this error:

ERROR:  function date_part(unknown, integer) does not exist
LINE 1: select DATE_PART('day', period_end - period_start) as days f...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 8

what's wrong with this? Thanks.

Barbaros Özhan · Accepted Answer · 2020-03-20 07:02:14Z

2

If you want to have the date difference using

select period_end - period_start as days from sales;

is enough.

DATE_PART() function looks for a date or a time value such as of type date or timestamp ( but not an integer ) as a second argument.

But in your case, the subtraction period_end - period_start yields an integer which causes the error
DATE_PART('day',<date>) extracts the day portion as an integer value

[ DATE_PART('day','2020-01-01::date') -> 1 ( not '01') ]

edited Mar 20, 2020 at 7:02

answered Mar 20, 2020 at 5:53

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anggi Permana Harianja Over a year ago

indeed, there is no need to overcomplicate.... just subtract using '-'

Collectives™ on Stack Overflow

postgresql - DATE_PART get no such function error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related