jeudi 22 janvier 2015

Timeline or time function in PostgreSQL


In MySQL I can use this select which show timeline view on data in my table/tables.



SELECT COUNT(atribute), connection_timestamp
FROM db
GROUP BY DAYOFYEAR(connection_timestamp)
ORDER BY connection_timestamp ASC;


But I need the same timeline view in PostreSQL database too. Can you help me please how I can obtain the same effect? Function DAYOFYER does not exist in PostgreSQL. Thank you for help.


Format would look like:



15.10.2014 0
16.10.2014 0
17.10.2014 4
18.10.2014 4
19.10.2014 2
20.10.2014 1
21.10.2014 5
22.10.2014 7


After this select:



SELECT
TO_CHAR(connection_timestamp, 'DD.MM.YYYY'),
COUNT(atribute)
FROM dionaea.connections
GROUP BY TO_CHAR(connection_timestamp, 'DD.MM.YYYY')
ORDER BY 1;


output is messy:



to_char | count
------------+--------
01.01.2015 | 5225
01.08.2014 | 4326
01.09.2014 | 14509
01.10.2014 | 2022
02.01.2015 | 2992
02.08.2014 | 6064
02.09.2014 | 9948
03.01.2015 | 2847




Aucun commentaire:

Enregistrer un commentaire