CODEWITHSUNDEEP
mysqlsql
reve berces
reve berces

Reputation: 349

getting data in database based on timestamp

I'm using time stamp in this format

    date_default_timezone_set('Asia/Manila');
    $date= date('F d, Y');
    $time= date('h:i:s A');

and it saves the data like this

November 5, 2019 12:46:03 AM

Now, I want to get and echo the monthly data,weekly and yearly data.

I tried using LIKE '$date%' but I cant get to make it work when it comes to weekly.

in this date's case, I want to get the data from NOVEMBER 5, 2019 and six days prior that(making it a week) I solved the monthly and yearly by saving it in another column and using that in my where statement, but I dunno how to do that in getting weekly datas

Upvotes: 2

Views: 6389

Answers (3)

Zak
Zak

Reputation: 7515

Per my comments above .. I am answering your request with an example ..

I have created a basic table and insert statement for you to play with .. 

CREATE TABLE `your_db`.`times` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mysql_timestamp` TIMESTAMP NULL,
  `hr_timestamp` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));

INSERT Some SAMPLE data:

INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2018-10-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2018-11-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2018-12-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2019-01-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2019-02-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2019-03-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2019-04-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2019-04-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2019-05-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2019-06-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2019-07-07 11:58:46', 'IE Oct 7, 2018');
INSERT INTO `your_db`.`times` (`mysql_timestamp`, `hr_timestamp`) VALUES ('2019-08-07 11:58:46', 'IE Oct 7, 2018');

An example of getting data by YEAR would be:

SELECT * FROM your_db.times WHERE YEAR(mysql_timestamp) = 2018;

An example of selecting a specific MONTH:

SELECT * FROM your_db.times WHERE MONTH(mysql_timestamp) = 04;

Example of selecting BETWEEN dates:

SELECT * FROM your_db.times WHERE mysql_timestamp BETWEEN '2018-12-01 00:00:00' AND '2019-04-30 00:00:00'

You can also use LT EQUAL and GT parameters .. IE:

SELECT * FROM your_db.times WHERE mysql_timestamp > '2018-12-01 00:00:00'

SELECT * FROM your_db.times WHERE mysql_timestamp <= '2018-12-01 00:00:00'

Upvotes: 2

nbk
nbk

Reputation: 49375

You have to convert the saved datet time stirn to a Date and use that for adding dayss and comapring

SELECT STR_TO_DATE("November 5, 2019 12:46:03 AM","%M %e, %Y %h:%i:%s %p") - INTERVAL 6 DAY;

Gives You as Result 2019-10-30 00:46:03

What You have to do is finally

WHERE  STR_TO_DATE("November 5, 2019 12:46:03 AM","%M %e, %Y %h:%i:%s %p")  
       < NOW() - INTERVAL 6 DAY

Upvotes: 0

GMB
GMB

Reputation: 222462

You seem to be storing dates as strings. Just don't. If you have dates, use the relevant datatype to store them in your database: in MySQL, you want the datetime datatype.

In php, you can generate a proper MySQL datetime with format specifier 'Y-m-d H:i:s'.

With this setup in place, it is easy to apply the filter you are looking for:

 mydate >= '2019-11-05' - interval 6 day and mydate <= '2019-11-05'

Upvotes: 3

Related Questions