How to check if the last item was registered today?

Navigation
0

I have a following table: 

+----+-----------------------+-----------------------+
| id |     description       |       created_at      |
+----+-----------------------+-----------------------+
| 5  | Game Of Thrones       |  2017-03-14 17:45:12  |
| 4  | Breaking Bad          |  2017-03-13 13:45:12  |
| 3  | Vikings               |  2017-03-12 12:45:12  |
| 2  | How I Met Your Mother |  2017-03-10 18:45:12  |
| 1  | Mr. Robot             |  2017-03-09 11:45:12  |
+----+-----------------------+ ----------------------+

Creating the table: 

CREATE TABLE myTable(
   int INTEGER PRIMARY KEY AUTOINCREMENT,
   description TEXT,
   created_at  DATETIME)

This table is incremented every day at a random time. I would like a query to check if a new item has already been registered today. In this case I still would not have a record because today it is 2017-03-15 11:14:22 , so I could register a new item.

Given that it is possible to get the last registered item using the query: 

select * from myTable order by created_at desc limit 1

How could you check if the last item was registered today based on the current time?

    
asked by anonymous 15.03.2017 / 15:16

2 answers

1

I would do so: 

SELECT
    *
FROM
    myTable
WHERE
    strftime ('%Y-%m-%d', created_at) = strftime ('%Y-%m-%d', 'now')
ORDER BY
    created_at DESC
LIMIT 1

Using STRFTIME(Format, Field) to format the date. If it is the same it will return the record.

    
15.03.2017 / 15:33
0

You can check today's date using the BETWEEN command. 

SELECT
  *
FROM myTable
WHERE created_at BETWEEN '2017-03-15 00:00:00' AND '2017-03-15 23:59:59'

This will bring up the records that have already been entered today. If you want the query to discover the date automatically, you can use the following: 

SELECT
  *
FROM myTable
WHERE created_at BETWEEN DATE_FORMAT('%Y-%m-%d 00:00:00', NOW()) AND DATE_FORMAT('%Y-%m-%d 23:59:59', NOW())
    
15.03.2017 / 15:47
Activate function when posting scheduled wordpress Android Studio does not recognize USB device [closed]