CODEWITHSUNDEEP
mysqlsql
user3790694
user3790694

Reputation: 353

sql retrieve data between timestamp

I'd like to extract all fields from my database that are between the ultima_modifica field (TIMESTAMP) and ultima_modifica + attesa(Enum '1','3','5')

enter image description here last_change is a timestamp

ultima_modifica is a (ENUM)

SELECT * FROM `my_table` WHERE ultima_modifica between ultima_modifica and ultima_modifica + interval attesa minute

In this way I retrieve all the results

The goal is to retrieve only the data that are between my timestamp (ultima_modifica) and my timestamp ultima_modifica + Enum (3)

Thanks for help

Upvotes: 2

Views: 141

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Try using TIMESTAMPADD:

SELECT *
FROM my_table
WHERE ultima_modifica BETWEEN ultima_modifica AND TIMESTAMPADD(MINUTE, attesa, ultima_modifica);

By using TIMESTAMPADD we get around the problem of working with the literal value needed for INTERVAL. This answer assumes that attesa is a column in your table whose units are minutes.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is your filtering condition:

WHERE last_change between last_change and last_change  + interval wait_time minute

Well, there are three possibilities:

  • If last_change or wait_time are NULL, then no rows are returned.
  • If wait_time is negative, then no rows are returned.
  • Otherwise, all rows are returned.

Why? Because any given time is always going to be "between" that time and a time a few minutes in the future.

So, your intentions are entirely unclear. Your query is generally going to return all rows. And always going to return all rows with non-NULL and non-negative values.

Upvotes: 0

Related Questions