CODEWITHSUNDEEP
sqlmysql
user1253538
user1253538

Reputation:

SQL queries with date types

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?

Upvotes: 0

Views: 119

Answers (2)

krock
krock

Reputation: 29619

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

Upvotes: 2

Michael Louis Thaler
Michael Louis Thaler

Reputation: 1874

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).

Upvotes: 1

Related Questions