converting sqlite to postgres

Question

I have another sqlite script that I need to convert to postgres. Take a gander if you can assist :)

SELECT count(*), strftime('%H', sentdate) as hour FROM latency l, contacts me 
   WHERE l.lat < 1 and datetime(sentdate) > datetime('2009-01-01') and datetime(sentdate)       
       < datetime('2011-02-01') and (me.id = l.replyuid or me.id = l.senduid) 
   GROUP BY hour ORDER BY hour asc;

gsiems · Accepted Answer · 2011-09-09 17:47:09Z

1

Assuming that sentdate is a date/timestamp field then this ought to work:

SELECT COUNT(*),
    date_part('hour', sentdate ) AS hour
FROM latency l,
    contacts me
WHERE l.lat < 1
    AND date_trunc ('day', sentdate) > DATE ( '2009-01-01' )
    AND date_trunc ('day', sentdate) < DATE ( '2011-02-01' )
    AND ( me.id = l.replyuid
        OR me.id = l.senduid )
GROUP BY date_part('hour', sentdate )
ORDER BY hour ASC;

answered Sep 9, 2011 at 17:47

gsiems

3,8301 gold badge25 silver badges24 bronze badges

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

2 Comments

SingleNegationElimination Over a year ago

If I were writing this query, I might try to make this an inner join (instead of comma join), and since there's an or in the join conditiion, I'd express that as a union, same query, but maybe more idiomatic sql.

gsiems Over a year ago

@TokenMacGuy - true enough. I very much prefer ANSI joins, however I was trying not to deviate too far from the original query...

Collectives™ on Stack Overflow

converting sqlite to postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related