Home > Enterprise >  Postgresql Newbie - Looking for insight
Postgresql Newbie - Looking for insight

Time:06-03

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.)

  • Related