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