CODEWITHSUNDEEP
sqlpostgresqltimetimestamp
Secco
Secco

Reputation: 15

subtract current_time from timestamp in PostgreSQL

I have a table where each entry has a timestamp. I am using this timestamp to calculate e.g. the number of entries per day in the last week. However, I do not really care whether the results that I get represent a particular day. The actual goal is to divide the entries into 24h-bins that I can compare and see whether there has been any significant change over time. Furthermore, since I am working with almost real-time data, I would like to perform this analysis at any time and also take into account the most recent entries. If I would just group the entries per day and perform the query in the middle of the day then I would get a not particularly insightful result for the current day.

My idea was now to subtract the current time from the timestamp of the entries and then do the grouping by days. This way I could get 24h-bins each of which represents a full 24h period and the youngest one also contains the most recent entries.

Something like this: created_on - current_time

Of course I cannot subtract a time from a timestamp. Is there a way to convert current_time into an interval? Or is there an entirely different approach that is easier?

Upvotes: 0

Views: 495

Answers (1)

user330315
user330315

Reputation:

Is there a way to convert current_time into an interval?

Yes, just cast it.

Note that the use of current_time is discouraged so it's better to use localtime instead.

You can do:

created_on - localtime::interval

But it seems you might just want to set the time part of the timestamp to 00:00:00 which you can do by using date_trunc()

date_trunc('day', created_on)

Upvotes: 1

Related Questions