3

For the purpose of creating a filter dropdown, I need to find out what the first and last dates are in a mysql table. I am referring to a field that has a date in it.

My first thought was run a query to pull ALL records, and then do some logic in php to find the first and last date.

Any better suggestions.. my solution seems like it would be very inefficient once there is a huge dataset.

Roeland
  • 3,698
  • 7
  • 46
  • 62

6 Answers6

10

Smallest date:

select date_field from table order by date_field asc limit 1;

Largest date:

select date_field from table order by date_field desc limit 1;

Both:

select min(date_field),max(date_field) from table;
Gaurav Gupta
  • 5,380
  • 2
  • 29
  • 36
4

Never pull all records! Use SQL for that:

SELECT your_time_column FROM your_table ORDER BY your_time_column ASC LIMIT 1

and

SELECT your_time_column FROM your_table ORDER BY your_time_column DESC LIMIT 1

or

SELECT MAX(your_time_column), MIN(your_time_column) FROM your_table

Roman Newaza
  • 11,405
  • 11
  • 58
  • 89
2

What about this?

SELECT MAX(`date`), MIN(`date`)
FROM your_table;

Index on a date will help.

Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
2

use mysql min() and max() to select the first and last date

select min(date),max(date) from table;
Manigandan Arjunan
  • 2,260
  • 1
  • 25
  • 42
1

Actually if you order the query by date SELECT * FROM table_name ORDER BY date in php you can easily get the first and last date:

first: $first_date = reset($result);
last: $last_date = end($result);

redmoon7777
  • 4,498
  • 1
  • 24
  • 26
1

You can make 2 queries (1 for first date, 1 for last date) and union them.

(SELECT the_date FROM table ORDER BY the_date ACS  LIMIT 1)
UNION
(SELECT the_date FROM table ORDER BY the_date DESC LIMIT 1)
Aziz
  • 20,065
  • 8
  • 63
  • 69