getting data in database based on timestamp

Question

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

Sorry, I think this question is still a bit vague. Could you provide more clarity on what you're looking for? Where is the data you're working with? Could this help you? https://stackoverflow.com/questions/8544438/select-records-from-now-1-day — richyen, Nov 11 '19 at 18:06
In order to do this to this you should use MySQL's built in `TIMESTAMP`, `DATE`, or `DATETIME` fields. Storing in `VARCHAR` makes it extremely difficult. The format in which you should store this is `yyyy-mm-dd hh:mm:ss` -- At that point you can use queries like `WHERE my_date_field > 2014` etc etc .. OR `WHERE my_filed BETWEEN` .. Making for much easier queries .. Conversely, if you need a human readable format, simply add a field to the DB in `varchar` so you have both MySQL format AND string format. — Zak, Nov 11 '19 at 18:09
Are you using type ```timestamp```? It sounds like you have plain text in your DB, which is not recommendable. Use ```timestamp``` and have a look at ```DAYOFWEEK()``` — Islingre, Nov 11 '19 at 18:10
@Zak can you please provide code for that, this is basically what my professor taught us so I dont have much to work with — reve berces, Nov 11 '19 at 18:12

score 3 · Accepted Answer · answered Nov 11 '19 at 18:09

3

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'

answered Nov 11 '19 at 18:09

GMB

216,147
25
84
135

1

i saved it in my database in a VARCHAR, then inserted it in database using VALUE(..,'$date $time',...) – reve berces Nov 11 '19 at 18:15
1

@Remaster: like I said, just don't. Create a `datetime` column instead, use a php expression like `date('Y-m-d H:i:s')` to generate a proper MySQL date, then insert it in your database. – GMB Nov 11 '19 at 18:17
1

should i use "mydate" in my statement or the column name of my database table? – reve berces Nov 11 '19 at 18:20
`mydate` stands for the name of your `datetime` column, you can replace it with the real name of that column. – GMB Nov 11 '19 at 18:22
Welcome @Remaster! Glad that it helped. – GMB Nov 11 '19 at 18:48

Zak · Answer 2 · 2019-11-11T18:34:30.870

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'

thank you I'll play with that to learn about it more. the other answer solved my problem but I upvoted you. THANK YOUUUU — reve berces, Nov 11 '19 at 18:40

score 0 · Answer 3 · answered Nov 11 '19 at 18:21

0

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

answered Nov 11 '19 at 18:21

nbk

45,398
8
30
47

1

I used this sql statment based on what you gave me "SELECT * FROM sales WHERE STR_TO_DATE("November 5, 2019 12:46:03 AM","%M %e, %Y %h:%i:%s %p") < NOW() - INTERVAL 6 DAY " it returned an error "syntax error, unexpected 'November' (T_STRING)" – reve berces Nov 11 '19 at 18:32
of course this works fine see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6272f173e51d32640ececf0200e062f9 – nbk Nov 11 '19 at 18:43

getting data in database based on timestamp

3 Answers3