Mysql unix timestamp select

Question

I have table with orders list. Each order have order date (timestamp 1331182800). For each order user can select automatic reorder by 30, 60 or 90 days (reorder field (int)). I need sql for cron to select all orders, that have order date = date + reorder. (date order + 30 or 60 or 90 days). Can You help me with this?

order date is a unix timestamp, and reorder is a day number, aren't they? — Sebas
– Sebas, Commented May 30, 2012 at 13:03
SELECT * FROM table_name WHERE UNIX_TIMESTAMP() >= (order_time_col + (reorder_days_col * 86400)) — DaveRandom
– DaveRandom, Commented May 30, 2012 at 13:04

YamahaSY · Accepted Answer · 2012-05-30 13:53:22Z

2

SELECT * FROM orders o WHERE DATE_FORMAT(DATE_SUB(NOW(), INTERVAL o.reorder DAY), '%d/%m/%Y') = DATE_FORMAT(FROM_UNIXTIME(o.date), '%d/%m/%Y')

edited to skip the seconds from the comparison. I don't have a MYSQL engine so some errors still could occur, if that was so I'm sorry about such inconvenient...

edited May 30, 2012 at 13:53

YamahaSY

2793 silver badges17 bronze badges

answered May 30, 2012 at 13:05

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

YamahaSY Over a year ago

its returns me empty result. i have an order with order date 28 may 2012 (1338183074) in reorder field i put 2 My query is: SELECT * FROM orders WHERE date_add(NOW(), INTERVAL reorder DAY) = FROM_UNIXTIME(ord_date)

Sebas Over a year ago

The query is also comparing the seconds and it doesn't obviously doesn't match; Update coming.

DaveRandom Over a year ago

@Sebas I don't have a MYSQL engine - Allow me to introduce you to SQLFiddle ;-)

Sebas Over a year ago

that was not your question ;) "order date = date + reorder"

heximal · Accepted Answer · 2012-05-30 13:05:18Z

0

timestamp is number of seconds passed from 1st Jan 1970 so in order convert timestamp value to days one needs to multiply it with 60 * 60 * 24 (= 86400)

answered May 30, 2012 at 13:05

heximal

10.5k5 gold badges49 silver badges73 bronze badges

Comments

itsmeee · Accepted Answer · 2012-05-30 13:09:01Z

0

Something like this should work:

SELECT * FROM orders WHERE `date` < UNIX_TIMESTAMP() - reorder*86400; # returns all orders, `date` field of which is older than 90 days ago

edited May 30, 2012 at 13:09

answered May 30, 2012 at 13:03

itsmeee

1,64511 silver badges12 bronze badges

Collectives™ on Stack Overflow

Mysql unix timestamp select

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related