On 05/21/2015 11:02 AM, Daniel Torres wrote:
Sorry, forgot to told you what I'm trying, I have climate data and want
to obtain mean temperature and total precipitation and that sort of
things per month and year. Think date_trunc is a good solution, but any
other advice would be very welcome.
As it turns out I am working on something similar with regards to school
days. A quick and dirty query:
SELECT
extract (
YEAR
FROM
school_day ) AS YEAR,
extract (
MONTH
FROM
school_day ) AS MONTH,
count (
school_day )
FROM
school_calendar
GROUP BY
extract (
YEAR
FROM
school_day ),
extract (
MONTH
FROM
school_day )
ORDER BY
extract (
YEAR
FROM
school_day ),
extract (
MONTH
FROM
school_day );
Reformatting courtesy of pgFormatter(http://sqlformat.darold.net/).
Results:
year | month | count
------+-------+-------
2005 | 3 | 7
2005 | 4 | 12
2005 | 5 | 17
2005 | 6 | 14
2005 | 7 | 11
2005 | 8 | 15
2005 | 9 | 16
2005 | 10 | 15
2005 | 11 | 17
2005 | 12 | 10
2006 | 1 | 15
2006 | 2 | 12
2006 | 3 | 18
2006 | 4 | 12
2006 | 5 | 18
2006 | 6 | 13
2006 | 7 | 11
2006 | 8 | 15
2006 | 9 | 15
2006 | 10 | 18
2006 | 11 | 13
2006 | 12 | 10
(I need to read more about time zones, I'm new at using postgresql)
Thank you,
Daniel
2015-05-21 12:45 GMT-05:00 Paul Jungwirth <pj@xxxxxxxxxxxxxxxxxxxxxxxx
<mailto:pj@xxxxxxxxxxxxxxxxxxxxxxxx>>:
You really shouldn't use WITHOUT TIME ZONE.
I'd like to know more about this. Can you say why? Are there any
articles you'd recommend? I'm fond of normalizing all times to UTC
and only presenting them in a time zone when I know the current
"perspective". I've written about that approach in a Rails context here:
http://illuminatedcomputing.com/posts/2014/04/timezones/
I find that this helps me to ignore time zones in most parts of my
application and cut down on my timezone-related bugs.
Thanks!
Paul
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx
<mailto:pgsql-general@xxxxxxxxxxxxxx>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
