Query with date range with a variable

Question

Query with date range with a variable

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I'm new to SQL and I'm breaking my head to put together a query that returns the results I want.

I have a field whose date format is as follows: 2018-12-13T18:01:16.573-02:00 .

I have a $Período variable whose values are (month / year): 12/2017 , 01/2018 , 02/2018 , etc.

This query worked:

SELECT *
    FROM nomedatabela
    WHERE TO_CHAR(colunadata, 'MM/YYYY') = ($PERIODO)

However, the period I want to query when selecting the variable is as follows:

When you select the variable whose value is 02/2018 , the query looks for the records of 06/01/2018 to 05/02/2018 ;
When you select the variable whose value is 03/2018 , the query looks for the records of 06/02/2018 to 05/03/2018 ;
and so on.

How can I do this?

sql

asked by anonymous 19.12.2018 / 21:28

2 answers

Task Scheduler on Heroku to run PHP script How to bring an image of a site in the Node API request?

score 1 · Answer 1

First you would need to control the start and end dates, as you do not want to rely on the full month (from 1 to 30/31 ). This is done by comparing colunadata to these two:

SELECT *
FROM nomedatabela
WHERE TO_CHAR(colunadata, '%d/%m/%Y') >= DATE_ADD(STR_TO_DATE(CONCAT('06/', $periodo), '%d/%m/%Y'), INTERVAL -1 MONTH)
  AND TO_CHAR(colunadata, '%d/%m/%Y') <= CONCAT('05/', $periodo)

Better explaining what happens in DATE_ADD(STR_TO_DATE(CONCAT('06/', $periodo), '%d/%m/%Y'), INTERVAL -1 MONTH) ..

CONCAT() - > adds the day to search ( 05 or 06 , for start and end of the period);
STR_TO_DATE() - > transforms the mounted string (ex "06/02/2018") into a date;
DATE_ADD() - > adds (removes, in case) a date period;
INTERVAL -1 MONTH - > selects the specified interval for the date_add (in this case - a month )

score 0 · Answer 2

Looking at a time range with the command below, all records will be returned within this timeframe.

SELECT * FROM table_name WHERE coluna_data BETWEEN 'data01' AND 'data02';

As you are only bringing month / year, it may be necessary to make a concatenation, for example you gave me it seems to be fixed dates from the 6th day of a certain month until the 5th of next month.

I do not know what to do with php, so I can not tell you how to work with the dates (probably you already know), but the sql that will query it the way you said it is this .

Source: link