I am in an introduction to sql class (using postgresql) and struggling to take simple queries to the next step. I have a single table with two datetime columns (start_time & end_time) that I want to extract as two date only columns. I figured out how to extract just the date from datetime using the following:
Select start_time,
CAST(start_time as date) as Start_Date
from [table];
or
Select end_time,
CAST(end_time as date) as End_Date
from [table];
Problem: I can't figure out the next step to combine both of these queries into a single step. I tried using WHERE but i am still doing something wrong. 1st wrong example
SELECT start_time, end_time
From baywheels_2017
WHERE
CAST(start_time AS DATE) AS Start_Date
AND (CAST(end_time AS DATE) AS End_Date);
Any help is greatly appreciated. Thanks for taking the time to look.
CodePudding user response:
You don't need to select the underlying field in order to later cast it; each field in the "select" clause is relatively independent. With the table created by:
CREATE TABLE test (
id SERIAL PRIMARY KEY,
start_time TIMESTAMP WITH TIME ZONE NOT NULL,
end_time TIMESTAMP WITH TIME ZONE NOT NULL
);
INSERT INTO test(start_time, end_time)
VALUES ('2022-10-31T12:30:00Z', '2022-12-31T23:59:59Z');
You could run the select:
SELECT
cast(start_time as date) as start_date,
cast(end_time as date) as end_date
FROM test;
(You can try this out on a website like DB-Fiddle.)