I am in the process of porting some python scripts to Mysql. Currently, the scripts I have queries a Pg database for entries (made) between certain dates.
The python script snippet of the query
++++++++++++++++++++++++
mainQuery = """
select distinct id,queue,owner,created,now(),age(now(),%s) from Tickets where
( (%s >= '%s'::timestamp) and ( %s <= '%s'::timestamp + '%s'::interval) )
and %s and %s(status='%s') order by id
""" %(queryField,queryField,date,queryField,date,age,typeId,statusModifier,status)
++++++++++++++++++++++++
To make this more readable, in plain sql, the query for 7 day old entries would be:
++++++++++++++++++++++++
select distinct id,queue,owner,created,now(),age(now(),resolved) from Tickets where
( (resolved >= '2003-08-26'::timestamp) and ( resolved <= '2003-08-26'::timestamp + ' 7 days'::interval) )
and queue=3 and (lastupdatedby=2) and (status='resolved') order by id
++++++++++++++++++++++++
The problem with this that it uses 'age', 'timestamp' and 'interval' functions that are specific to Pg or just not implemeted in Mysql (version 3.23.56). What is the Mysql equivilent query? More than that, any qeuey that SQLsomeYear compliant would be great.
I am considering the dates to be: resolved>='somedate1 sometime1' and resolved<='somedate2 sometime2' But, I still have a problem with: age(now(),resolved)
More info (from a Pg session) to show what the Pg functions do +++++++++++++++++++++++ rt3=# select '2003-08-26'::timestamp + ' 7 days'::interval; ?column? --------------------- 2003-09-02 00:00:00
mysql> select date_format( date_add('2003-08-26', interval 7 day), "%Y-%m-%d %H:%i:%s") as mydate; +---------------------+ | mydate | +---------------------+ | 2003-09-02 00:00:00 | +---------------------+
rt3=# select '2003-08-26'::timestamp; timestamp --------------------- 2003-08-26 00:00:00
mysql> select from_unixtime(unix_timestamp('2003-08-26'));
+---------------------------------------------+
| from_unixtime(unix_timestamp('2003-08-26')) |
+---------------------------------------------+
| 2003-08-26 00:00:00 |
+---------------------------------------------+
rt3=# select age(now(),'2003-08-26'::timestamp); age ------------------------ 6 days 10:14:43.421275
+++++++++++++++++++++++
Time to read mysql manual ? ;)
http://www.mysql.com/doc/en/Date_and_time_functions.html
Regards, Joseph Bueno
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
