CODEWITHSUNDEEP
phpmysql
Bibin James
Bibin James

Reputation: 87

Getting data from mysql query based on Date duration

I Have one mysql table with five fields. fields are alert_id, creation_time, assigned_to, alert_duration, alert_body. In a single query i need to get alert_body based on

  1. assigned_to(where assigned_to ='401').

  2. current date in between creation_time and creation_time + alert_duration

alert_duration is numbers of days. So In my query i need to fetch the data only if the current date in between that time gap(creation_time to creation_time +3days).

Example:

current Date: 2/9/2015

creation_time: 31/08/2015

alert_duration : 3

So if i execute the query on or before 2/9/2015, should get the result with data. But in case i execute the query by tomorrow, then query will give empty result.

Here i add the sql format also

Table structure for table tbl_alerts

CREATE TABLE IF NOT EXISTS tbl_alerts ( alert_id varchar(20) NOT NULL, creation_time timestamp(6) NOT NULL, assigned_to varchar(30) NOT NULL, alert_duration int(3) NOT NULL, alert_body varchar(200) NOT NULL, PRIMARY KEY (alert_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Dumping data for table tbl_alerts

INSERT INTO tbl_alerts (alert_id, creation_time, assigned_to, alert_duration, alert_body) VALUES ('1000001', '2015-09-01 23:26:41.000000', '401', 3, 'good morning, have a nic day'), ('1000002', '2015-08-15 08:31:37.000000', '401', 2, 'History papers will be distributed on Friday.');

Please help me , Thanks in Advance

Upvotes: 0

Views: 77

Answers (2)

PerroVerd
PerroVerd

Reputation: 955

Use the TO_DAYS function

SELECT * FROM tbl_alerts 
WHERE 
   assigned_to = '401' 
  AND
   TO_DAYS(now()) - TO_DAYS(creation_time) <= alert_duration

Not sure if the comparision is with the alert_duration or with the hardcoded 3 days

Upvotes: 0

eggyal
eggyal

Reputation: 126035

In a single query i need to get alert_body

select alert_body from my_table

based on

  1. assigned_to(where assigned_to ='401').
where assigned_to = '401'
  1. current date in between creation_time and creation_time + alert_duration
and current_date between creation_time and creation_time +
          INTERVAL alert_duration DAY

Upvotes: 1

Related Questions