0

Hello,

I have one query with timestamp. I got below error.

select users.login, 
FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(ticket_history.create_time)-UNIX_TIMESTAMP(ticket.create_time))) 
from ticket_history_type
inner join ticket_history on ticket_history.history_type_id=ticket_history_type.id
inner join ticket on ticket.id = ticket_history.ticket_id
inner join users on users.id = ticket_history.change_by
where
ticket.create_time BETWEEN '2014-10-01' AND '2014-10-15'
AND
ticket_history.name ='%%new%%Fermeture réussie%%'
group by users.id

error: function unix_timestamp(timestamp without time zone) does not exist

Thanks in advanced for your input.

2
Contributors
8
Replies
28
Views
3 Years
Discussion Span
Last Post by PriteshP23
0

Well, i tried from this blog but i have both functions in the same query.

Not working :(

Any idea to get result in single pl sql query ?

Edited by PriteshP23: code

0

@pritaeas

There is a function "to_timestamp".

select users.login, to_timestamp( AVG( extract(epoch from ticket_history.create_time) - extract(epoch from ticket.create_time) ) ) from ticket_history_type
inner join ticket_history on ticket_history.history_type_id=ticket_history_type.id
inner join ticket on ticket.id = ticket_history.ticket_id
inner join users on users.id = ticket_history.change_by
where
ticket.create_time BETWEEN '2014-10-01' AND '2014-10-15'
AND
ticket_history.name ='%%new%%Fermeture réussie%%'
group by users.id

Error: I got all dates like this: "1970-01-01 06:54:49.544299+01"

Why all rows with same date "1970-01-01" ? It should take value of column.

I have date in this format in both table columns: '%Y-%m-%d %H:%M:%S'

Thanks in advanced..!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.