Psql help with comparing timestamps

Dev Talk
1

This is my first time dealing with postgres, and I’m trying to write queries that will generate reports for SoundExchange. First I was curious to find all the files that were played during my reporting period, but Postgres keeps returning the same error: “ERROR: operator does not exist: timestamp without time zone = integer”

select DISTINCT ON (f.id) f.id, f.artist_name, f.track_title, f.album_title, f.label, f.isrc_number
from cc_files as f, cc_playout_history as h, cc_timestamp as t
where f.id = h.file_id AND h.starts = t.id AND (t.timestamp > ‘2018-03-04 00:00:00’ AND t.timestamp < ‘2018-03-17 23:59:59’);

Is there a different function I should use or some sort of modifier I have to attach to my timestamp values in order for this to work? Thanks.

2

I think you need to use to_timestamp(text, text) to convert the date in string format to a timestamp w/ a timezone see PostgreSQL: Documentation: 8.4: Data Type Formatting Functions

3

I tried it all of these ways:

select DISTINCT ON (f.id) f.id, f.artist_name, f.track_title, f.album_title, f.label, f.isrc_number
from cc_files as f, cc_playout_history as h, cc_timestamp as t
where f.id = h.file_id AND h.starts = t.id AND (t.timestamp > ‘2018-03-04 00:00:00’ AND t.timestamp < ‘2018-03-17 23:59:59’);

SELECT DISTINCT ON (f.id) f.id, f.artist_name, f.track_title, f.album_title, f.label, f.isrc_number
FROM cc_files AS f, cc_playout_history AS h, cc_timestamp AS t
WHERE f.id = h.file_id AND h.starts = t.id AND (t.timestamp >= to_timestamp(‘2018-03-04 00:00:00’, ‘YYYY-MM-DD HH:MM:SS’)
AND t.timestamp < to_timestamp(‘2018-03-17 23:59:59’, ‘YYYY-MM-DD HH:MM:SS’));

SELECT DISTINCT ON (f.id) f.id, f.artist_name, f.track_title, f.album_title, f.label, f.isrc_number
FROM cc_files AS f, cc_playout_history AS h, cc_timestamp AS t
WHERE f.id = h.file_id AND h.starts = t.id AND (t.timestamp >= timestamp ‘2018-03-04 00:00:00’ AND t.timestamp < timestamp ‘2018-03-17 23:59:59’);

SELECT DISTINCT ON (f.id) f.id, f.artist_name, f.track_title, f.album_title, f.label, f.isrc_number
FROM cc_files AS f, cc_playout_history AS h, cc_timestamp AS t
WHERE f.id = h.file_id AND h.starts = t.id AND (date_part(t.timestamp) >= 1520121600 AND date_part(t.timestamp) < 1521331200);

SELECT DISTINCT ON (f.id) f.id, f.artist_name, f.track_title, f.album_title, f.label, f.isrc_number
FROM cc_files AS f, cc_playout_history AS h, cc_timestamp AS t
WHERE f.id = h.file_id AND h.starts = t.id AND (date_trunc(t.timestamp) >= ‘2018-03-04’ AND date_trunc(t.timestamp) < ‘2018-03-18’);

And I get the same error each time: operator does not exist:timestamp without time zone = integer.
I thought, maybe I need to convert my beginning and end dates to unix timestamps to do integer comparison, but that didn’t work either.

4

Actually, my attempt to join cc_timestamp and cc_playout_history is wrong: “AND h.starts = t.id”.

I’ll try again later. Frustrating. Would be a lot easier if listener values and playout history were in one table, but I understand with multiple streams, that’s not practical.

Edit: I’m a complete idiot. Never mind!