CODEWITHSUNDEEP
postgresql
Baljot Singh
Baljot Singh

Reputation: 133

How to convert unix timestamp column to date format in PostgreSQL?

I'm trying to obtain the results in yyyy-mm-dd format from unixtimestamp column but getting in yyyy-mm-dd hh:mm:ss

My data in receive_time column is following:

recieve_time

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

Following is my query:

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)

Getting this:

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    |

My requirement is following:

date        |count|

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

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

Upvotes: 8

Views: 16984

Answers (3)

Shawn.X
Shawn.X

Reputation: 1353

I think the simplest way to convert a unix timestamp to date format in PostgreSQL is below:

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

So your complete SQL would be:

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

Notice: if there is a index created on your receive_time column, you'd better do not use the function on receive_time when it located at the where clause to filter rows, it will lead to fail to use index when execute SQL, the way above in my SQL is a better way. Good luck !

Upvotes: 7

Jan Mich&#225;lek
Jan Mich&#225;lek

Reputation: 41

For formating date you should use function "to_char" https://www.postgresql.org/docs/9.6/functions-formatting.html It support time formating, or you can use extract to separate date part.

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

Upvotes: 4

Laurenz Albe
Laurenz Albe

Reputation: 246063

Cast the result to date, both in the SELECT list and the GROUP BY clause:

CAST(to_timestamp(recieve_time) AS date)

Upvotes: 10

Related Questions