sqlQuery where timestamp is a specific date

Question

Hy guys I have this table: VisitedCity

id	timestamp	name	city
1	2022-02-02 10:02:23	Mark	LA
2	2022-01-15 08:45:01	Phil	NY
3	2022-02-05 11:09:45	John	MI

My query :

SELECT * FROM VisitedCity
WHERE timestamp = '2022-02-02'

But it doesn't return any rows. How Could I do it?

Review date functions https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date — P.Salmon, Feb 21 '22 at 09:46
@Pawan.Java This can do for a quick and dirty query, but using string manipulation functions to deal with dates almost never pays off in the long term. Code becomes harder to maintain, performance suffers and it's easy for bugs to slip in. — Álvaro González, Feb 21 '22 at 10:09
@ÁlvaroGonzález I know but given the problem statement, he was looking for something quick itself. — Pawan.Java, Feb 21 '22 at 11:34
@gianluca23 your question is a copy of this one: https://stackoverflow.com/questions/3976837/mysql-timestamp-select-date-range/3976902 Make good use ;) — Pawan.Java, Feb 21 '22 at 11:37

score 1 · Accepted Answer · answered Feb 21 '22 at 09:45

1

You can't use = if you don't want an exact match. You need to query for an interval:

SELECT *
FROM VisitedCity
WHERE timestamp >= '2022-02-02 00:00:00' AND timestamp < '2022-02-03 00:00:00'

answered Feb 21 '22 at 09:45

Álvaro González

Thanks, I have modified only the last part in `timestamp < DATE_ADD( '2022-02-02 ', INTERVAL 1 DAY) ` – gianluca23 Feb 21 '22 at 10:41

1 Answers1