CODEWITHSUNDEEP
mysqldatetime
hermanvn
hermanvn

Reputation: 33

MySQL Date calculation

I have exhausted my searches looking for a solution to this MySQL date calculation. My client wants to know how many deals were done in the past week, month and year.

It is a SQL based script (but in MySQL language) I am busy with, so don't bother with the Select, From etc. The datetime_created field (in the script) is already in UnixTime, while the timestamp(now) is used to calculate current date minus 7 days. As the formula result can be negative as well, I only require the result between 0.01 and 7.

My formula for the past week is listed below. Any ideas?

(registrations.datetime_created -unix_timestamp(now())-604800)/86400

Upvotes: 1

Views: 950

Answers (4)

hermanvn
hermanvn

Reputation: 33

The Where clause was not accepted at all, and I had to change some logic.Thanks all, but the final script was a concoction from everyone, and is as follows:

registrations.datetime_created > UNIX_TIMESTAMP(ADDDATE(NOW(),INTERVAL -1 WEEK))

Upvotes: 0

Travesty3
Travesty3

Reputation: 14479

This isn't tested, but I think it will do what you want:

SELECT
    q1.dealsInLastWeek,
    q2.dealsInLastMonth,
    q3.dealsInLastYear
FROM
    (SELECT COUNT(*) AS dealsInLastWeek FROM registrations WHERE FROM_UNIXTIME(datetime_created) > DATE_ADD(NOW(), INTERVAL -1 WEEK)) q1,
    (SELECT COUNT(*) AS dealsInLastMonth FROM registrations WHERE FROM_UNIXTIME(datetime_created) > DATE_ADD(NOW(), INTERVAL -1 MONTH)) q2,
    (SELECT COUNT(*) AS dealsInLastYear FROM registrations WHERE FROM_UNIXTIME(datetime_created) > DATE_ADD(NOW(), INTERVAL -1 YEAR)) q3

Upvotes: 1

Joan Vidal
Joan Vidal

Reputation: 1

Test with INTERVAL

SELECT (unix_timestamp(now()-INTERVAL '7' DAY)),(unix_timestamp(now()-INTERVAL '1' MONTH)),(unix_timestamp(now()-INTERVAL '1' YEAR));

WHERE registrations.datetime_created>(unix_timestamp(now()-INTERVAL '7' DAY))

Upvotes: 0

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

How about using UNIX_TIMESTAMP / ADDDATE() / between?

Edit slight expansion to clarify

SELECT
    COUNT(*)
FROM registrations
WHERE registrations.datetime_created
    -- will go back to now() - a month INCLUDING time.  -1 WEEK/MONTH/YEAR etc all works here
    BETWEEN UNIX_TIMESTAMP(ADDDATE(NOW(),INTERVAL -1 WEEK)) 
    AND UNIX_TIMESTAMP(NOW())                                   
    -- will start at now() including time

By specifying the start and end timestamp within the WHERE criteria, MySQL is able to use an index on the datetime_created field (if one is present) to do this very efficiently. If, instead, you have a WHERE registrations.datetime_created*1235/12515 (an operation on the field) within the WHERE then you will see it returning slower as MySQL will need to scan the whole table to determine which rows to return.

Upvotes: 2

Related Questions