CODEWITHSUNDEEP
sqlpostgresql
user12724454
user12724454

Reputation:

How to get all rows during a day with a specific column value

I have multiple rows in a table with a column named "created" that has a timestamp in the format "1309494407". I need to do a SQL query to get how many rows there are that has a certain value of column called "category" with the value of "book". Basically I need to know how many books are created every day in the database.

How is this accomplished with PostgreSQL?

My schema:

Items(id, category, created)

This is what I've tried so far:

SELECT
    *
FROM
    my_schema.items
WHERE
    (category = 'books' AND TO_TIMESTAMP(created) < NOW() - INTERVAL '1 days')

And it doesn't work I get problem:

function to_timestamp(character varying) does not exist

Upvotes: 0

Views: 450

Answers (2)

Belayer
Belayer

Reputation: 14886

What a wonder example of why Unix epoch is such a poor choice for storing dates, and a text representation of the number at that. Always store dates/timestamps with the appropriate type. If needed the epoch is easily retrieved from a date, but deriving the date from epoch is - well ugly. And actual timestamp even uglier.
If you happen to to be on version 12 you can at least capture the date in a table column itself, without having to change the code already using the existing column. Create a generated column that derives the actual date. (Guess that would make an interesting round trip, given a date derive the epoch then given the epoch derive the date).

alter table items 
  add created_date date generated always as 
      ('1970-01-01'::timestamp + (created::integer/ 86400) * interval '1 day')  
  stored;

Now this query reduces itself to the simple and straight forward:

select i.created_date,count(*)
  from my_schema.items i
 where i.category = 'books' 
 group by i.created_date
 order by i.created_date;

It also has the additional benefit of making all the Postgres date handling functions available.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Basically I need to know how many books are created every day in the database.

Your timestamp looks like a Unix epoch. If so, you can get what you want using simple arithmetic:

SELECT floor(i.created / (24 * 60 * 60)) as dy, COUNT(*)
FROM my_schema.items i
WHERE i.category = 'books' 
GROUP BY dy;

If you like, you can convert it to a calendar date using:

SELECT '1970-01-01' + floor(i.created / (24 * 60 * 60)) * interval '1 day' as dy, COUNT(*)
FROM my_schema.items i
WHERE i.category = 'books' 
GROUP BY dy;

Upvotes: 0

Related Questions