Mysql SELECT last few days?

Mastering date and time functions

Working with time-sensitive data? MySQL’s date and time functions got your back. Let's dive deeper into their usage and practical applications.

Interval variations

With MySQL, you can switch between different types of intervals - be it an hour, a minute, or even a second:

-- Party like it's the last 24 hours 🥳
SELECT * FROM your_table
WHERE timestamp_column >= NOW() - INTERVAL 1 DAY;

-- Because every minute matters ⏳
SELECT * FROM your_table
WHERE timestamp_column >= NOW() - INTERVAL 70 MINUTE;


Remember, NOW() gives you date and time, including an accurate second value.

Timezone challenges

MySQL is like an old man trying to set his watch, it always consults its own timezone. So, whenever your application wrestles with multiple timezones, remember to convert the data:

-- When your server thinks it's Sherlock Holmes, detecting timezones
SELECT * FROM your_table
WHERE timestamp_column >= CONVERT_TZ(NOW(), @@session.time_zone, '+00:00') - INTERVAL 3 DAY;


Precision with UNIX_TIMESTAMP

The UNIX_TIMESTAMP function navigates the daylight saving time manipulations by filtering records within a specific timeframe in seconds:

-- Ever wondered how many seconds are there in 3 hours? Well, now you know!
SELECT * FROM your_table
WHERE UNIX_TIMESTAMP(timestamp_column) >= UNIX_TIMESTAMP() - 10800;


Advanced querying techniques

The secret to mastering MySQL date and time handling is in understanding its nuances and leveraging its functions efficiently.

DATE_SUB for subtraction

You can also use DATE_SUB to subtract dates, like a subtraction race, but with dates. Fancy, right?

SELECT * FROM your_table
WHERE date_column >= DATE_SUB(CURDATE(), INTERVAL 3 DAY);


DATEDIFF warnings

DATEDIFF may sound cool, but it's the bad boy of date-time functions as it calculates the difference in days completely disregarding the time within a day:

-- Breaking hearts 💔 and ignoring time
SELECT * FROM your_table
WHERE DATEDIFF(CURDATE(), date_column) >= 3;


Sub-second precision

Need sub-second timing? Let MICROSECOND come to your rescue for querying in milliseconds:

-- Because milliseconds matter for some of us
SELECT * FROM your_table
WHERE timestamp_column >= (NOW(6) - INTERVAL 3 DAY);


Efficient date-time querying

Timestamps and dates are tricky, but with a few good practices, they can be wielded effectively in your data retrieval arsenal.

The power of TIMESTAMP

If you have a TIMESTAMP column, MySQL automatically takes care of the UTC conversions:

-- Who needs a timezone converter when you have MySQL?
SELECT * FROM your_table
WHERE timestamp_column >= CURDATE() - INTERVAL 3 DAY;


Shielding against invalid dates

Avoid the embarrassment of invalid dates in your results. Validate your date columns diligently:

-- Because no one likes to see '0000-00-00' in their results 🥴
SELECT * FROM your_table
WHERE date_column >= CURDATE() - INTERVAL 3 DAY
AND date_column <> '0000-00-00';


Indexing for performance

Indexes are like the Table of Contents of your database. They are essential for lightning-fast result retrieval, especially in large databases:

-- When MySQL sprints like Usain Bolt 🏃
-- Ensure date_column is indexed for best performance
ALTER TABLE your_table ADD INDEX (date_column);
