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

Question

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   |

There is no such column data type unixtimestamp in Postgres. — Basil Bourque
– Basil Bourque, Commented Jun 14, 2019 at 7:32
Don't store integers instead of dates. Use the correct type. SQL, the language, and all databases have date types. The integer you stored may be called "unix timestamp" but we don't know if it represents seconds, or milliseconds - never mind the epoch. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 14, 2019 at 7:33
What's worse, the query you want to perform will have to scan the entire table without benefiting from indexes, because to_timestamp(recieve_time) >='2019-05-01 00:00:00' works on the output of a function, not the indexed value itself. To make this query run fast you'd have to convert the date values into integers — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 14, 2019 at 7:37

Laurenz Albe · Accepted Answer · 2019-06-14 07:38:15Z

11

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

CAST(to_timestamp(recieve_time) AS date)

answered Jun 14, 2019 at 7:38

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shawn.X · Accepted Answer · 2021-10-22 06:43:17Z

8

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 !

edited Oct 22, 2021 at 6:43

answered Jun 14, 2019 at 7:49

Shawn.X

1,3637 silver badges17 bronze badges

1 Comment

Shawn.X Over a year ago

@BaljotSingh I have given some advise in my answer above, hope helpful for you. :D

Jan Michálek · Accepted Answer · 2019-06-14 07:45:03Z

4

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')

answered Jun 14, 2019 at 7:45

Jan Michálek

411 bronze badge

1 Comment

BJW Over a year ago

drop a couple of zeros SELECT to_char(to_timestamp( 1525202258000/1000), 'yyyy-mm-dd')

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related