convert sqlite to postgres query

Question

Can someone help me convert this sqlite query to a postgres query?

SELECT count(*), count(*), date(date, '-'||strftime('%w',date)||' days') as date
FROM emails as m, contacts as me
WHERE datetime(date) > datetime('2010-08-25')
  and datetime(date) < datetime('2011-08-25')
  and (me.id = m.fr)
  and me.email like '%gmail.com%'
GROUP BY date
ORDER BY date asc

update, I found the answer:

select count(*), (m.date::date - extract(dow from m.date)::int) as dat
from emails as m join contacts as me on m.fr = me.id
where m.date > '2010-08-25'
  and m.date < '2011-08-25'
  and me.email like '%gmail.com%'
group by dat
order by dat

There seems to be two count(*), not sure why either. the date type is "timestamp with timezone" — locoboy
– locoboy, Commented Aug 25, 2011 at 18:42

Community · Accepted Answer · 2017-05-23 12:01:55Z

1

The strftime business was taken care of elsewhere so we only have to sort out the datetime(...) stuff here and compensate for date being a timestamp. And I'll switch to an explicit join condition (rather than the implicit one in the WHERE clause) while I'm here.

select count(*), count(*), m.date::date - extract(dow from m.date)::int as date
from emails as m join contacts as me on m.fr = me.id
where m.date > '2010-08-25'
  and m.date < '2011-08-25'
  and me.email like '%gmail.com%'
group by m.date
order by m.date asc

PostgreSQL can compare timestamps with ISO8601 date strings on its own so you don't need any casting or reformatting for the comparisons.

The two count(*) still look a bit funny to me but I don't know the context that the query is used in so the duplicates might make sense.

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Aug 25, 2011 at 19:33

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

6 Comments

locoboy Over a year ago

for some reason this isn't quite it. when you are saying group by date it doesn't really group it and add the count together.

mu is too short Over a year ago

@cfarm54: Do you have any sample data and the schemas? We have the date column, the date function, and the date alias; PostgreSQL and SQLite might be making different choices about which is meant in various situations.

locoboy Over a year ago

sure the schema for emails is:

(id integer primary key autoincrement,         fr int references contacts(id), subj text, date datetime, mid text unique,         reply text references msgs(mid), multipart bool);

the schema for contacts is: (id INTEGER PRIMARY KEY AUTOINCREMENT, name text, email text not null); . I am also getting confused with the term 'date'; between the method call and the variable, it's very confusing :P.

mu is too short Over a year ago

I added the table prefix to the various date column uses, see if that doesn't anything useful. A PostgreSQL timestamp has a higher resolution than SQLite's datetime, that might be causing some grouping strangeness; try group by m.date::date and see what that does. And using date as a column name is almost criminal, you might want to have a few words with whoever is responsible for choosing the original column names.

locoboy Over a year ago

idk if this helps, but i think what the query is trying to do is: 1. modify the date for each day to the end of the week (sunday) then group each of the counts by date. That way you are basically counting the total number of emails sent for the 7 day period.

|

Collectives™ on Stack Overflow

convert sqlite to postgres query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related