Postgresql – In SQL(postgresql) How to group based on a “timestamp without time zone” column

postgresql

I am trying to write some queries to group things based on each month in postgresql.

Say we have a table "crimes" which has 2 columns "activity date"(timestamp without time zone) and "zipcode"(character varying(5)), how to query the number of crimes for each month given a zipcode?

eg:
table "crimes":

activity date               zipcode
2014-11-22 00:52:00           12345
2014-10-22 00:52:00           12345
2014-10-24 00:52:00           12345
2014-12-22 00:52:00           54321

input: given zipcode"12345"

output: return

month        count
2014-10        2
2014-11        1

Best Answer

Try:

select
    extract(year from activity_date) as year,
    to_char(activity_date, 'Mon') as month,
    count(*) as count
from
    crimes
group by
    1,extract(month from activity_date);

Related Solutions

Postgresql – hibernate mapping for postgresql “timestamp without time zone”

I don't know Hibernate enough to comment on that part, but you could perhaps work around the error by overriding the datatypes in the INSERT statement:

<sql-insert callable="true">{call insert_wal (?::text, ?::numeric, <snip>?::timestamp, )::timestamp, <snip>)}</sql-insert>

You probably don't need to override all the fields, of course, just those causing problems.

Postgresql – Difference between timestamps with/without time zone in PostgreSQL

The differences are covered at the PostgreSQL documentation for date/time types. Yes, the treatment of TIME or TIMESTAMP differs between one WITH TIME ZONE or WITHOUT TIME ZONE. It doesn't affect how the values are stored; it affects how they are interpreted.

The effects of time zones on these data types is covered specifically in the docs. The difference arises from what the system can reasonably know about the value:

With a time zone as part of the value, the value can be rendered as a local time in the client.
Without a time zone as part of the value, the obvious default time zone is UTC, so it is rendered for that time zone.

The behaviour differs depending on at least three factors:

The timezone setting in the client.
The data type (i.e. WITH TIME ZONE or WITHOUT TIME ZONE) of the value.
Whether the value is specified with a particular time zone.

Here are examples covering the combinations of those factors:

foo=> SET TIMEZONE TO 'Japan';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+09
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 06:00:00+09
(1 row)

foo=> SET TIMEZONE TO 'Australia/Melbourne';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+11
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 08:00:00+11
(1 row)

Best Answer

Related Solutions

Postgresql – hibernate mapping for postgresql “timestamp without time zone”

Postgresql – Difference between timestamps with/without time zone in PostgreSQL

Related Topic