[Solved] Subtract set value of hours from a Time Field

[Dalke55]

Sliderule solved a similar issue I posted yesterday but i found that I need a different Function for a TIME field

I am trying to find the correct syntax to run a UPDATE on a TIME field. Format HH:MM:SS
Split Database HSQLDB_2.3.2
AOO 4.1.10
Windows 10

Table Name: TBL-CHART-DATA
Field Name: CHRT-TIME

I need to subtract 7 hours from each record TIME field on a select set of records

I looked in the HSQLDB User Guide but could not deduce the proper function format
TIMESTAMPADD
TIMESTAMPADD ( <tsi datetime field>, <numeric value expression>, <datetime value expr>)

UPDATE "TBL-CHART-DATA" AS "TBL-CHART-DATA"
SET "CHRT-TIME" = TIMESTAMPADD ("CHRT-TIME", -7, HOUR)
WHERE "ENTRYID" < 2270

SELECT
"TBL-CHART-DATA".*,
TIMESTAMPADD ("CHRT-TIME", -7, HOUR) as "TIME_MINUS_7_HOURS"
FROM "TBL-CHART-DATA"
WHERE "ENTRYID" < 2270

Thank You for your help

[Sliderule]

Please try the following:

Code: Select all

UPDATE "TBL-CHART-DATA" AS "TBL-CHART-DATA"
   SET "CHRT-TIME" = CAST(DATEADD('hour', -7, CAST('1970-09-01 ' || "CHRT-TM" as TIMESTAMP)) as TIME) 
WHERE "ENTRYID" < 2270

Explanation:

1. The third argument of the DATEADD function is a TIMESTAMP, so, will CAST your column "CHRT-TM"" as a TIMESTAMP by including a DATE followed by a SPACE . . . and . . . returning the calculated value to a TIME, using a CAST statement.
2. This is NOT a Query, but, may be run from Tools -> SQL...
3. I suggest your first try the 'changes' as a Query, to confirm what you see is what you want your update to look like :
   

   Code: Select all

   SELECT 
      "TBL-CHART-DATA".*,
      CAST(DATEADD('hour', -7, CAST('1970-09-01 ' || "CHRT-TM" as TIMESTAMP)) as TIME) as  "TIME_MINUS_7_HOUR"
   FROM "TBL-CHART-DATA"
   WHERE "ENTRYID" < 2270
   

4. Make sure I typed all your table and column names correctly . . . for the ones you need.
5. The use of DATEADD HSQL function is NOT a part of the Embedded HSQL database ( HSQL 1.8.0.10 ), so, it is only available with HSQL SPLIT starting with HSQL 2.2.9.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[Dalke55]

Thank you Sliderule ! That has solved my issue.