Convert unix timestamp to datetime

Hey guys,

I’m connecting PostgreSQL to the grafana, and I have a query to get current_date and used it for variables. The query as:

select distinct date(created_at) as date from event_logging where date(created_at) > CURRENT_DATE - INTERVAL ‘5’ day order by date(created_at) desc

Why the result is unix timestamp? How i convert it to date time?

Hi,

For Postgresql there a function

created_at::TIMESTAMPTZ

So, you can change your query like below:

select distinct date(created_at::TIMESTAMPTZ) as date from event_logging where date(created_at) > CURRENT_DATE - INTERVAL ‘5’ day order by date(created_at) desc

Regards,
Fadjar Tandabawana

Hi bang @fadjar340 ,

The result is still in unix timestamp.

The column actually timestamptz already
image

Only in grafana the result was in unix timestamp

Hi…

Try this:

select distinct to_timestamp(created_at)::date as date from event_logging where date(created_at) > CURRENT_DATE - INTERVAL ‘5’ day order by date(created_at) desc

Perhaps this helps you…

Regards,
Fadjar Tandabawana

Currently, got this error bang

Hi Bang…

Could you run the query in the SQL client like dbeaver?

And what is the result?

Regards,
Fadjar Tandabawana