How to check if last item was registered today?

Asked

Viewed 96 times

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  |
+----+-----------------------+ ----------------------+

Table creation:

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 which checks whether a new item has already been registered today. In this case there would still be no registration as today is 2017-03-15 11:14:22, then I could register a new item.

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

select * from myTable order by created_at desc limit 1

How could I verify that the last item was registered today based on the current time?

sqlite

  • @Acklay, doing a max on id, does not solve?

  • try this way select Description from myTable Where created_at = datetime()

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 the STRFTIME(Format, Field) to format the date. If equal will return the record.

  • Once I tried to use the now() on sqlite and it didn’t work. See again here and tell you.

  • In fact, error occurred again: no such function: now

  • It was just 'now'.

  • not tested with 'now' but created a function that does the same purpose of 'now' and worked right. Thanks +1

0

You can refer to today’s date using the command BETWEEN.

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

This will bring back 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())

Browser other questions tagged sqlite

You are not signed in. Login or sign up in order to post.