Convert unix timestamp to datetime

Grafana PostgreSQL
, ,
1

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?

2

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

3

Hi bang @fadjar340 ,

The result is still in unix timestamp.

image
image1445×818 53.5 KB

The column actually timestamptz already
image

Only in grafana the result was in unix timestamp

4

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

5

Currently, got this error bang

image
image1677×803 62 KB

1 Like
6

Hi Bang…

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

And what is the result?

Regards,
Fadjar Tandabawana

7

Hey did you find solution? i am facing similar problem

8

Try converting it into char like this:

select distinct to_char(created_at::timestamp,'YYYY-MM-DD HH24:MI:SS')) as date from event_logging where date(created_at) > CURRENT_DATE - INTERVAL ‘5’ day order by date(created_at) desc