Stack Exchange
What's proper way to convert this PostgreSQL code to MySQL
I'm trying to convert a PostgreSQL code to MySQL and I'm a little stuck. The section I'm having issues with looks like
SELECT (SELECT COUNT(1) FROM table1 WHERE id = o.id AND state = 'processing') AS number
          FROM table2 o
          WHERE TO_TIMESTAMP(time) >= '2018-03-19'  AND TO_TIMESTAMP(time) <= '2018-03-20'

This works perfectly fine in PostgreSQL. Converting it to MySQL I tried
SELECT (SELECT COUNT(1) FROM table1 WHERE id = o.id AND state = 'processing') AS number
                 FROM table2 o
          WHERE FROM_UNIXTIME(time) >= '2018-03-19'  AND FROM_UNIXTIME(time) <= '2018-03-20'

But unfortunately the code runs for ever and gives inacurate results.
I also tried 
SELECT (SELECT COUNT(1) FROM table1 l JOIN table2 o ON  l.id = o.id WHERE state = 'processing' AND  FROM_UNIXTIME(time) >= '2018-03-19'  AND FROM_UNIXTIME(time) <= '2018-03-20') AS number
                 FROM table2 o
          WHERE FROM_UNIXTIME(time) >= '2018-03-19'  AND FROM_UNIXTIME(time) <= '2018-03-20'

Which still doesn't give an accurate result. What's proper way to convert it to MySQL?

What is the datatype of the time column in the MySQL table? And what are representative values that are stored. (Without that, it's not possible to tell if the SQL is well formed. For performance, we want to avoid wrapping a column references inside functions in predicates... much better to do any transformation on the literal side, that will allow MySQL to use a range scan operation on an appropriate index. Wrapping the column in a function, that forces MySQL to evaluate the expression on every row in the table.

For large sets, we need appropriate indexes available in order to get reasonable performance, and especially true for a correlated subquery that is going to be executed numerous times.

@spencer7593 thanks for the response. I don't think I'm understanding what you mean completely.The time column is of datatype int(10) unsigned. I don't think that is the issue though (but I may be wrong) because I've used that for other queries and it worked fine. I think the issue might be somewhere along " table1 WHERE id = o.id "

In a predicate (condition in a WHERE clause), wrapping a column reference in a function ... FROM_UNIXTIME(col) = '2018-03-19' forces MySQL to evaluate the function on every row in the table. MySQL optimizer can't see past that to see that the result would be the same as if we had written  col = UNIX_TIMESTAMP('2018-03-19'). With the function around the column, MySQL sees that as an instruction to perform that function on every value of col. And with the latter, MySQL can do a range scan operation on an index with leading column of col.

SELECT  num
    FROM (SELECT tablea.id, 
        CASE WHEN (count(tablec.id))IS NULL THEN 0 ELSE (count(tablec.id)) END as num  
        FROM (SELECT id, 
                FROM table2 o
                WHERE DATE(FROM_UNIXTIME(time))>= '2018-03-19' 
                AND DATE(FROM_UNIXTIME(time))<= '2018-03-20' 
        ) as tablea 
        LEFT JOIN (SELECT distinct(id), 
                action 
                FROM table1  
                WHERE state = 'processing' 
        ) as tableb ON tablea.id = tableb.id  
        LEFT JOIN (SELECT id, 
                action 
                FROM table1  
                WHERE state = 'processing'  
        ) as tablec ON tablea.id = tablec.id  
    GROUP BY tablea.id
    ) as tt


I recommend referencing the bare column in the predicate, and do the conversion on the literal side. 
If column time is defined as integer type, and is unix-style integer number of seconds since 1970-01-01 00:00:00, we can convert the literal '2018-03-19' on the right side into an integer number of seconds.
That will enable MySQL to perform a range scan operation on an index that has time as the leading column.
I also recommend qualifying all column references, to avoid ambiguity and as an aid to a future reader.
SELECT ( SELECT COUNT(1)
           FROM table1 n
          WHERE n.id = o.id 
            AND n.state = 'processing'
       ) AS number
  FROM table2 o
 WHERE o.time >= UNIX_TIMESTAMP('2018-03-19')
   AND o.time <  UNIX_TIMESTAMP('2018-03-20')

In terms of performance, the correlated subquery in the SELECT list is going to eat our lunch, because it will get executed for every row returned by the outer query.
We use EXPLAIN to see the execution plan. For large sets, we are going to want to have suitable indexes available.  For example, covering indexes:
 ... ON `table2` (`time`,`id`)

 ... ON `table1` (`id`,`state`)

We expect to see efficient "range" operation and "Using index" in the Extra column, indicating that the query is satisfied from the index, without lookups to the data pages.
Also, we're assuming the tables are using InnoDB, and the buffer pool is sized appropriately.
Also note that I did a "less than" comparison rather than a "less than or equal to" on the high side. That's the typical pattern I use... if I'm also going to run a query to get numbers for March 20, I don't want include the same row(s) in counts for both March 19 and March 20.

If table2 has a PRIMARY KEY (or UNIQUE KEY) or some column (or set of columns) I would be tempted to avoid the correlated subquery and do a JOIN operation with conditional aggregation. 
SELECT IFNULL(SUM(n.state='processing'),0) AS `number`
  FROM table2 o 
  LEFT 
  JOIN table1 n 
    ON n.id = o.id
 WHERE o.time >= UNIX_TIMESTAMP('2018-03-19')
   AND o.time <  UNIX_TIMESTAMP('2018-03-20')
 GROUP BY o.unique_key


Thanks for the response. I really appreciate it! I found something that works finally.
