I have this table with a date column:
| fecha | hora | folio |
|---|---|---|
| 2022-03-04 | 16:40 | 1 |
| 2022-04-05 | 18:20 | 2 |
| 2022-04-06 | 13:20 | 3 |
I need to extract the year and month an used, so I used this query:
select fecha, extract(month from fecha) = 3 as mes, EXTRACT(YEAR FROM fecha) = 2022 as anio, hora, folio from table;
I get:
| fecha | mes | anio | hora | folio |
|---|---|---|---|---|
| 2022-03-04 | true | true | 16:40 | 1 |
| 2022-04-05 | false | true | 18:20 | 2 |
| 2022-04-06 | false | true | 13:20 | 3 |
I need only the rows where mes and anio are true, but the WHERE clause doesn't recognize mes and anio as columns of the table.
Instead, I got the following error:
ERROR: column "mes" does not exist
LINE 1: ..._medicion from medicion where r_fisico = true and mes = true...
^
SQL state: 42703
Character: 353
I need this values for a <input type='month' />
An approach using sub-query.
Basically wrap your query with another select, and place the WHERE condition outside.
SELECT * FROM (
SELECT
fecha,
EXTRACT(MONTH FROM fecha) = 3 AS mes,
EXTRACT(YEAR FROM fecha) = 2022 AS anio,
hora,
folio
FROM table
) t
WHERE t.anio = true and t.mes = true;
Your problem stems from the sequence each phase of the SQL statement is processed. The column alias is assigned as the select list is processed however where clause is processed before the select list. Thus the assigned alias does not exist when the where clause is processed. You basically have three options:
Extract the values (with alias) in a sub-select (or CTE) then select with where referencing the alias.
select *
from (
select fecha
, extract(month from fecha) = 3 as mes
, extract(year froM fecha) = 2022 as anio
, hora
, folio
from table
)
where mes = true
and anio = true;
Repeat the evaluation for mes and anio (without the actual alias).
select fecha
, extract(month from fecha) = 3 as mes
, extract(year froM fecha) = 2022 as anio
, hora
, folio
from table
where extract(month from fecha) = 3
and extract(year froM fecha) = 2022 ;
Rewrite with where referencing just the date itself.
select fecha
, extract(month from fecha) = 3 as mes
, extract(year froM fecha) = 2022 as anio
, hora
, folio
from table
where date_trunc('month', fecha) = date '2022-03-01';
Note None tested.