CODEWITHSUNDEEP
mysqldatabaseautomation
sfactor
sfactor

Reputation: 13062

Checking time-stamps if they are from today in MySQL

I have a database with several tables. Each of these tables has a column 'created' which contains time-stamps of when that particular row was created in the database.

Now, I want to create a MySQL script that checks once every week if there is data coming into these tables. So, there should be data coming everyday. How do I create a MySQL script that allows me to do this for all the tables in the database?

Note: Remember I want to do this for all the tables in the database with a single script. That's the main thing I want to know.

Upvotes: 1

Views: 86

Answers (2)

Bruno Flávio
Bruno Flávio

Reputation: 778

i use this approach for a table called call, with a column of timestamp type called systemdate:

SELECT * FROM `call` WHERE DATE(`systemdate`) = DATE(NOW());

mysql DATE() statement gets the datepart of a datetime or timestamp field.

Sorry, just noticed that you want to check if atleast there is an entry for each of the days in the previous week.

you can use this query to check the prevous days individually:

yesterday:

SELECT * FROM `call` WHERE DATE(`systemdate`) = DATE(NOW()) - 1;

before yesterday:

SELECT * FROM `call` WHERE DATE(`systemdate`) = DATE(NOW()) - 2;

Or you can check the whole week at once:

SELECT * FROM `call` WHERE DATE(`systemdate`) > DATE_SUB(CURDATE(), INTERVAL 7 DAY)  GROUP BY DATE(`systemdate`);

This will return one result for each day, so if you have 7 results you'll know at least an entry was made on each day.

Upvotes: 1

Bohemian
Bohemian

Reputation: 424993

select * from table
where created between subdate(current_date, interval 7 day) and current_date;

Selecting datetimes up to current_date includes everything up to the start of "today" (ie "the previous midnight").

Upvotes: 0

Related Questions