[Solved] Subtract set value of hours from a Timestamp field

[Dalke55]

I am trying to find the correct syntax to run a UPDATE on a time stamp field.
Split Database HSQLDB_2.3.2
AOO 4.1.10
Windows 10

Table Name: TBL-ENTRY
Field Name: ENTRY-DT:TM

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

THIS is what I have tried using [Queires, Tools, SQL...]

UPDATE "TBL-ENTRY" AS "TBL-ENTRY"
SET "ENTRY-DT:TM" = DATESUB (TIMESTAMP "ENTRY-DT:TM", INTERVAL -7 HOUR)
WHERE "ENTRYID" < 2270

Status results 1: user lacks privilege or object not found: DATESUB

Thank You for your help

[Sliderule]

Please try the following:

Code: Select all

UPDATE "TBL-ENTRY" AS "TBL-ENTRY"
SET "ENTRY-DT:TM" = DATEADD('hour', -7, "ENTRY-DT:TM")
WHERE "ENTRYID" < 2270

Explanation:

1. The difference is, I used the function DATEADD rather than DATESUB, DATESUB is NOT an HSQL function
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-ENTRY".*,
      DATEADD('hour', -7, "ENTRY-DT:TM") as "DATEADD_MINUS_7_HOURS"
   FROM "TBL-ENTRY"
   WHERE "ENTRYID" < 2270
   

Sliderule

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

[Dalke55]

Work like a charm
THANK YOU!