ERROR: operator does not exist: timestamp with time zone / integer

Question

When I want to divide a time into integer I get this error: ERROR: operator does not exist: timestamp with time zone / integer.

Is there any function in postgres to divide the time to the integer?

For example: "2013-11-14 07:46:15+03:30" is the timestamp with time zone and I want the output of "2013-11-14 07:46:15+03:30" / 2 = "1991-12-08 17:38:07.5+03:30"

So what is the expected output of `2021-04-16 09:26:55 / 42`? — , Apr 16 '21 at 07:27
You probably want a TIME or INTERVAL datatype, instead of a TIMESTAMP WITH TIME ZONE. You can cast your TIMESTAMP WITH TIME ZONE to TIME without any problems, just use CAST() — Frank Heikens, Apr 16 '21 at 08:01

score 0 · Answer 1 · answered Apr 16 '21 at 07:31

0

I think the issue is that it is not being stored as a number. If I understand what you are asking, you want the time since epoch instead of something like 01/01/1992.

How to convert date time into unix epoch value in Postgres? discusses a few ways to convert it to epoch, but the key ones are either select extract(epoch from ts) from data or select date_part('epoch', ts) from data; where data is your table, and ts is your timestamp. Then you should be able to divide by an Integer.

answered Apr 16 '21 at 07:31

JNichols

66
6

Even if you convert it to unix epoch, what sense does it make to divide a timestamp by an integer. I would be very interested in what the expected result of `2021-04-16 09:26:55 / 42` is supposed to be – Apr 16 '21 at 07:39
My understanding is that in epoch, `2021-04-16 09:26:55` would become `1618590415` which should be divisible by an int – JNichols Apr 16 '21 at 07:42
Yes, but what is the _meaning_ of dividing that with an integer? It simply doesn't make sense. Dividing it by e.g. 42 yields and epoch that is equivalent to `1971-03-23 01:57:47` – Apr 16 '21 at 07:45
Ah, I see what you are saying. I am curious on the why as well, but I think this should provide a solution to whatever they are trying to do. – JNichols Apr 16 '21 at 07:47
So I am new to Stack Overflow, and I get that context is nice, but does the why really matter for a question like this? I ask since it has a clear problem statement of `Is there any function in postgres to divide the time to the integer?` – JNichols Apr 16 '21 at 07:53
It's likely an [X-Y Problem](https://meta.stackexchange.com/questions/66377) – Apr 16 '21 at 07:59

score 0 · Accepted Answer · answered Apr 16 '21 at 08:55

0

I found a way for that question:

SELECT to_timestamp(extract(epoch from DATE)/2) AS date FROM Table

Where DATE is your timestamp with time zone and Table is your given table. The type of result is a timestamp with time zone.

answered Apr 16 '21 at 08:55

Balive13

99
1
8

ERROR: operator does not exist: timestamp with time zone / integer

2 Answers2