Best way to calculate dates from results?

Question

I have a database table. It has several hundred entries and each entry is timestamped. I'm looking to only output each day and not have it duplicate any days.

Data:
2009-02-04 06:20:27
2009-02-04 06:20:27
2009-02-05 06:20:27
2009-02-07 06:20:27
2009-02-07 06:20:27
2009-02-07 06:20:27

Output should only be (time is irrelevant):
2009-02-04 06:20:27
2009-02-05 06:20:27
2009-02-07 06:20:27

What is the best way to accomplish this? Should I really be cycling through each row and comparing one from another? Just doesn't seem efficient. Any ideas/tips?

Answer 1

can't you just select for a "DISTINCT" timestamp in your query?

Or if the times vary on each day (not sure from your example) you can convert the timestamp column to just a date of the fly using the mysql DATE function. Keep in mind this may prevent use of an index if it's used on that column.

eg.

SELECT DISTINCT DATE(timestamp_column) FROM table

This will remove the time part of the timestamp.

Answer 2

You would want to do this on the database level using the mysql DISTINCT keyword:

SELECT DISTINCT date
FROM table

This will make it so if there are duplicate dates, only one is returned. Read more about it here.

Answer 3

This will print out double timestamps, but should be distinct to only the date.

SELECT DISTINCT CAST(timestamp AS DATE), timestamp, FROM table.

Answer 4

The DATE() Function will extract the date from a datetime field. You can use the result of that to group. Used like this:

SELECT fields FROM table WHERE condition GROUP BY DATE(datefield)

Answer 5

SELECT DISTINCT CAST(data AS DATE)
    FROM DataTable;

This drops the time portion, and then gives the unique DATE values.