英文:
Selecting specific year data from date table (PostgreSQL)
问题
我正在尝试编写一个查询，从数据表中选择特定年份的数据，但是我遇到了一个错误。
我试图找到2022年停放的汽车每小时费用的平均值。我编写了这个查询，但是我遇到了一个错误。
我的查询：（不起作用）
SELECT AVG(HourlyFee) FROM ParkingFees
WHERE Region IN (SELECT Region FROM ParkingHistory
WHERE EXTRACT(YEAR FROM Date) = 2022);
错误：
错误：函数year（没有时区的时间戳）不存在
第3行：WHERE EXTRACT(YEAR FROM Date) = 2022;
^
提示：没有函数与给定的名称和参数类型匹配。您可能需要添加显式类型转换。
SQL状态：42883
字符：100
这是数据库：
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);
我正在尝试查找2022年停放的汽车每小时费用的平均值。我该如何编写正确的查询？
英文:
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?
答案1
得分: 1
你可以使用EXTRACT()，如@NickW已经提到的，或在你的WHERE条件中使用>=和<：
WHERE Date >= '2022-01-01'::timestamp AND Date < '2023-01-01'::timestamp
英文:
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
答案2
得分: 0
要从时间戳中获取年份，您需要使用EXTRACT函数，例如：
SELECT EXTRACT(YEAR FROM TIMESTAMP '2016-12-31 13:30:15');
英文:
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');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论