Comment convertir une colonne timestamp unix au format date dans PostgreSQL ?

Question

J'essaie d'obtenir les résultats au format aaaa-mm-jj à partir de la colonne unixtimestamp mais j'obtiens le format aaaa-mm-jj hh:mm:ss.

Mes données dans la colonne receive_time sont les suivantes :

recieve_time

1557866863  |
1557866863  |
1557866863  |
1557866863  |
1557866864  |
1557866864  |
1557866864  |
1557866864  |

Voici ma question :

SELECT
to_timestamp(recieve_time) as date, count(*)
 FROM public.cdrs WHERE 
usage_type='0800 Voice Incoming' 
and to_timestamp(recieve_time) >='2019-05-01 00:00:00'
AND to_timestamp(recieve_time) <'2019-06-01 00:00:00'
AND main_bzd >0 
group by to_timestamp(recieve_time)

Je vais chercher ça :

date                 |count|

-------------------|-----|

2019-05-01 00:00:2 |1    |
2019-05-01 00:03:2 |1    |
2019-05-01 01:20:0 |1    |
2019-05-01 01:21:1 |1    |
2019-05-01 01:53:0 |1    |
2019-05-01 02:16:5 |1    |
2019-05-01 02:33:5 |1    |
2019-05-01 02:39:4 |1    |
2019-05-01 02:55:3 |1    |
2019-05-01 03:32:5 |1    |
2019-05-01 03:35:0 |1    |

Mon besoin est le suivant :

date        |count|

------------|-----|

2019-05-01  |19   |
2019-05-02  |15   |
2019-05-03  |17   |

Answer 1

Transformez le résultat en date tant dans le SELECT et la liste des GROUP BY clause :

CAST(to_timestamp(recieve_time) AS date)

Answer 2

Je pense que la manière la plus simple de convertir un timestamp unix au format date dans PostgreSQL est la suivante :

select to_timestamp(1557866863)::date;
 to_timestamp 
--------------
 2019-05-15
(1 row)

Donc votre SQL complet serait :

select
     to_timestamp(recieve_time)::date as date,
     count(*)
from 
    public.cdrs 
where 
    usage_type='0800 Voice Incoming'
    and receive_time >= extract(epoch from cast('2019-05-01 00:00:00' as timestamptz))
    and receive_time <  extract(epoch from cast('2019-06-01 00:00:00' as timestamptz))
    and main_bzd >0 
group by 
    to_timestamp(recieve_time)::date

Remarque : s'il y a un index créé sur votre receive_time il vaut mieux ne pas utiliser la fonction sur la colonne receive_time lorsqu'il se trouve au where pour filtrer les lignes, cela conduira à l'échec de l'utilisation de l'index lors de l'exécution du SQL, la façon ci-dessus dans mon SQL est une meilleure façon. Bonne chance !

Answer 3

Pour formater la date, vous devez utiliser la fonction "to_char". https://www.postgresql.org/docs/9.6/functions-formatting.html Il supporte le formatage de l'heure, ou vous pouvez utiliser l'extrait pour séparer la partie date.

SELECT to_char(to_timestamp( 156049813956389/100000), 'yyyy-mm-dd')