SQL queries with date types

Question

I am wanting to do some queries on a sales table and a purchases table, things like showing the total costs, or total sales of a particular item etc.

Both of these tables also have a date field, which stores dates in sortable format(just the default I suppose?)

I am wondering how I would do things with this date field as part of my query to use date ranges such as:

The last year, from any given date of the year

The last 30 days, from any given day

To show set months, such as January, Febuary etc.

Are these types of queries possible just using a DATE field, or would it be easier to store months and years as separate tex fields?

Answer 1

If a given DATE field MY_DATE, you can perform those 3 operation using various date functions:

1. Select last years records

SELECT * FROM MY_TABLE
WHERE YEAR(my_date) = YEAR(CURDATE()) - 1

2. Last 30 Days

SELECT * FROM MY_TABLE
WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) < MY_DATE

3. Show the month name

SELECT MONTHNAME(MY_DATE), * FROM MY_TABLE

Answer 2

I have always found it advantageous to store dates as Unix timestamps. They're extremely easy to sort by and query by range, and MySQL has built-in features that help (like UNIX_TIMESTAMP() and FROM_UNIXTIME()).

You can store them in INT(11) columns; when you program with them you learn quickly that a day is 86400 seconds, and you can get more complex ranges by multiplying that by a number of days (e.g. a month is close enough to 86400 * 30, and programming languages usually have excellent facilities for converting to and from them built into standard libraries).