Group by year only from field which is in Java Date class format in PostgreSQL

Question

I am working in PostgreSQL, I have table name my_table schema

CREATE TABLE my_table
(
  my_table_id character varying(50) NOT NULL,
  bar_id character varying(50),
  beer_id character varying(50),
  status_id character varying(50),

  updated_at bigint,

  CONSTRAINT my_table_id PRIMARY KEY (craft_bar_beer_id ),
  CONSTRAINT my_table_bar_id_fk FOREIGN KEY (bar_id)
      REFERENCES table_bar (bar_id) MATCH Unknown
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT beer_status_id_fk FOREIGN KEY (status_id)
      REFERENCES status (status_id) MATCH Unknown
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT beer_id_fk FOREIGN KEY (beer_id)
      REFERENCES beer (beer_id) MATCH Unknown
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Now the output is count of beer_id which are having status as '1' and '2' and group by date (here suppose the year 2016 is provided or for last 10 years the record should get displayed)

count(beer_id)   status     year
4                  1        2014
5                  1        2015
3                  2        2014
6                  2        2016

But here the field updated_at is a bigint which is time in milliseconds derived from a Java Date object:

Date dateValue = null;
SimpleDateFormat formatter = new SimpleDateFormat(dateFormat);
formatter.setTimeZone(TimeZone.getTimeZone("GMT"));
dateValue = formatter.parse(dateStr);
return dateValue.getTime();

Query I tried:

SELECT count(cbeer.beer_id) AS beers, cbeer.status_id AS Status
FROM my_table cbeer
INNER JOIN bar cb ON cb.bar_id = cbeer.bar_id
WHERE status_id IN ('1','2')
GROUP BY cbeer.status_id;

OUTPUT:

beers Status
5      1
1      2

How to query to get the date wise records too when date is in long and I want it to group by it by last 10 years. And if year provided for ex: 2015, group by it 2015 and get the month related records for that year.

Storing a timestamp as a long was not such a good idea to begin with (you now see why). But you can convert a unix epoch value to a timestamp using to_timestamp(). See the manual for details postgresql.org/docs/current/static/functions-formatting.html — a_horse_with_no_name
You say time is expressed in milli-seconds and stored in a long. Can you show us a sample of this? And are you sure it is milli-seconds and not seconds? — Patrick
@Patrick Yes i have converted date in string to time in milliseconds and store it in datatype bigint. what sample you want me to show you? — Kamini
Can you show an example of how you convert this string to a bigint? The reason is that "date" (+ time, presumably) stored in a bigint requires some point of reference. What date + time is represented by a value of 0? The old standard in PostgreSQL is to store a timestamp in bigint format with the Unix default 0 point in seconds. Hence my questions. — Patrick
Date dateValue = null;SimpleDateFormat formatter = new SimpleDateFormat(dateFormat); formatter.setTimeZone(TimeZone.getTimeZone("GMT")); dateValue = formatter.parse(dateStr); return dateValue.getTime(); — Kamini

Patrick Patrick · Accepted Answer · 2015-07-30T07:30:43

The Java Date.getTime() function returns the object as a long in milliseconds since 1 January 1970. Luckily this is the same epoch that PostgreSQL uses so you can simply divide the value by 1,000 and convert to a timestamp to have full access to the PostgreSQL date and time functions:

to_timestamp(updated_at / 1000.)

Now your queries. For the last 10 years:

SELECT count(cbeer.beer_id) AS beers,
       cbeer.status_id AS Status,
       extract('year' from to_timestamp(updated_at / 1000.)) AS year
FROM my_table cbeer
INNER JOIN bar cb ON cb.bar_id = cbeer.bar_id
WHERE status_id IN ('1','2')
  AND extract('year' from to_timestamp(updated_at / 1000.)) > 2005
GROUP BY 2, 3
ORDER BY 2, 3, 1;

For the month:

SELECT count(cbeer.beer_id) AS beers,
       cbeer.status_id AS Status,
       extract('month' from to_timestamp(updated_at / 1000.)) AS month
FROM my_table cbeer
INNER JOIN bar cb ON cb.bar_id = cbeer.bar_id
WHERE status_id IN ('1','2')
  AND extract('year' from to_timestamp(updated_at / 1000.)) = 2015
GROUP BY 2, 3
ORDER BY 2, 3, 1;

Group by year only from field which is in Java Date class format in PostgreSQL

1 Answers