Hi,
Found some samples of doing calcs with timestamps, but none of how to convert the datediff of 2 timestamps from integer to double or float. The extract or convert functions dont seem to work for this.
Basically I need something like DATEDIFF(minute,timestamp1,timestamp2) which gives me an integer, now I need to divide by 60 because I want 1.23 hours. If I just do a DATEDIFF(hour,...) that will give me just 1.
Thinking of embedding the datediff into a CAST but am not able to find the syntax anywhere.
Any hints will be appreciated
thanks
[Solved]Converting timestamps
[Solved]Converting timestamps
Last edited by floris v on Fri Dec 20, 2019 4:56 pm, edited 2 times in total.
Reason: Added green checkmark, floris v, moderator
Reason: Added green checkmark, floris v, moderator
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Converting timestamps
Include the decimals as in SELECT datediff('minute', "a", "b")/60.00 AS "Hours" FROM "Somewhere"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Converting timestamps
In addition to the sound advice given by Villeroy, you might consider using the following HSQL functions, depending on your specific needs:
- ROUND
- CEILING
- FLOOR
- TRUNC
- TRUNCATE
- TIMESTAMPDIFF
Re: Converting timestamps
Thank you gentlemen, didn t dawn on me to divide by 60.00 only tried 60. Its weird that one always thinks of complex solutions first !
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend