skip to Main Content

I am trying to write a query for select a specific year data from data table, but I am getting an error.

I am trying to reach the average of the hourly fee for cars parked in 2022. I wrote this query, but I am getting an error.

My query: (Not working)

SELECT AVG(HourlyFee) FROM ParkingFees
WHERE Region IN(SELECT Region FROM ParkingHistory
                WHERE YEAR (Date) ='2022');
                

Error:

ERROR:  function year(timestamp without time zone) does not exist
LINE 3:     WHERE YEAR (Date) ='2022');
                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 100

Here is the database:


CREATE TABLE ParkingHistory(RegNo varchar(7), Date timestamp(0), Hours int, Region varchar(9));

INSERT INTO ParkingHistory
    (RegNo, Date, Hours, Region)
VALUES
    ('B17SNR', '2023-01-30 00:00:00', 15, 'Bucharest'),
    ('B02JSH', '2023-01-28 00:00:00', 8, 'Cluj'),
    ('B74BFK', '2023-01-30 00:00:00', 24, 'Cluj'),
    ('BH84JSB', '2022-12-26 00:00:00', 9, 'Bucharest'),
    ('IF92AAN', '2022-12-26 00:00:00', 7, 'Bucharest'),
    ('OT74ISH', '2022-12-14 00:00:00', 15, 'Cluj'),
    ('CT56UUH', '2022-12-11 00:00:00', 23, 'Ploiesti'),
    ('BR82IIS', '2022-12-05 00:00:00', 24, 'Bucharest'),
    ('B73YHT', '2022-12-05 00:00:00', 24, 'Bucharest'),
    ('B83UEH', '2022-12-05 00:00:00', 21, 'Bucharest');


CREATE TABLE ParkingFees(Region varchar(9), HourlyFee int);  

INSERT INTO ParkingFees
    (Region, HourlyFee)
VALUES
    ('Bucharest', 20),
    ('Cluj', 16),
    ('Ploiesti', 12),
    ('Pitesti', 16),
    ('Timisoara', 9);


CREATE TABLE Cars(RegNo varchar(7), Model varchar(7), IsResident int);  

INSERT INTO Cars
    (RegNo, Model, IsResident)
VALUES
    ('B17SNR', 'T-ROC', 1),
    ('B02JSH', 'Ibiza', 0),
    ('B74BFK', 'Ibiza', 1),
    ('BH84JSB', 'Sandero', 0),
    ('IF92AAN', 'Logan', 0),
    ('OT74ISH', 'T-ROC', 1),
    ('CT56UUH', 'Arona', 1),
    ('BR82IIS', 'Tiguan', 1),
    ('B73YHT', 'Logan', 1),
    ('B83UEH', 'Duster', 0);
    

CREATE TABLE CarModels(Model varchar(7), Manufacturer varchar(10), FabricationYear int);

INSERT INTO CarModels
    (Model, Manufacturer, FabricationYear)
VALUES
    ('Logan', 'Dacia', 2016),
    ('Sandero', 'Dacia', 2018),
    ('Duster', 'Dacia', 2019),
    ('Leon', 'Seat', 2017),
    ('Ibiza', 'Seat', 2019),
    ('Ateca', 'Seat', 2020),
    ('Arona', 'Seat', 2021),
    ('Golf', 'Volkswagen', 2017),
    ('Polo', 'Volkswagen', 2019),
    ('Tiguan', 'Volkswagen', 2020),
    ('T-ROC', 'Volkswagen', 2021);

I am trying to find the average of the hourly fee for cars parked in 2022. How can I write the true query?

2

Answers


  1. To get the year from a timestamp you need to use the EXTRACT function e.g.

    SELECT EXTRACT(YEAR FROM TIMESTAMP '2016-12-31 13:30:15');
    
    Login or Signup to reply.
  2. You could use EXTRACT() as @NickW already mentioned, or use >= and < in your where condition:

    WHERE Date >= '2022-01-01'::timestamp AND Date < '2023-01-01'::timestamp
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search