[Solved]Converting timestamps

Creating tables and queries
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved]Converting timestamps

Post by gkick »

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
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
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting timestamps

Post by Villeroy »

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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Converting timestamps

Post by Sliderule »

In addition to the sound advice given by Villeroy, you might consider using the following HSQL functions, depending on your specific needs:
  1. ROUND
  2. CEILING
  3. FLOOR
  4. TRUNC
  5. TRUNCATE
  6. TIMESTAMPDIFF
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Converting timestamps

Post by gkick »

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
Post Reply