We help IT Professionals succeed at work.

Get a date range with mysql

Web IT
Web IT asked
on
This is the query:
SELECT DATE(FROM_UNIXTIME(@unix_date_time:=substring_index(meta_value, '|',1))) AS "date" FROM wp_postmeta WHERE meta_key LIKE "etimeclockwp-away_%"

Open in new window


it  shows a table called date it currently show all dates
2019-11-14
2019-12-02
2019-12-16
2019-11-25
2020-01-08
I have been trying to sort date range using some stack overflow  example(


SELECT DATE(FROM_UNIXTIME(@unix_date_time:=substring_index(meta_value, '|',1))) AS "date" FROM wp_postmeta WHERE "date" >=`2019-11-14` AND "date" <=`2019-12-16` meta_key LIKE "etimeclockwp-away_%"

Open in new window


But it did not work.
What am I missing?

Goal = get any date range
Comment
Watch Question

NorieAnalyst Assistant

Commented:
Which dates do you want to select?

Your example query will only return records where the date is 3rd January 2016 and there are no records with that date in your sample data.

Author

Commented:
I updated the code in the question  to reflect the column.
NorieAnalyst Assistant

Commented:
As far as I know you can't use aliases in the WHERE clause in MySQL.

So try replacing "date" with meta_value.
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Hi,

With an aliased column, you need to be using the HAVING keyword, not the WHERE keyword. Try this:

SELECT DATE(FROM_UNIXTIME(@unix_date_time:=substring_index(meta_value, '|',1))) AS date
FROM wp_postmeta
WHERE meta_key LIKE 'etimeclockwp-away_%'
HAVING date >= '2019-11-14' AND date <= '2019-12-16'

Open in new window


And make sure your quotes are correct. Don't quote the column names or aliases and use single-quotes for the data values, not backticks!

Author

Commented:
Thank you very much you might answered one question ,but this answer helped a ton of people get salary reports faster an in an easier way from a wired way to create a time clock.
Keep it up.