DelphiFan Magazine

Top Menu

Main Menu

  • DELPHI
  • CODE SAMPLES
  • FIREMONKEY
  • DATABASE
  • RELEASES
  • VIDEOS
  • REVIEW
  • TECH NEWS

logo

DelphiFan Magazine

  • DELPHI
  • CODE SAMPLES
  • FIREMONKEY
  • DATABASE
  • RELEASES
  • VIDEOS
  • REVIEW
  • TECH NEWS
  • How to add a server application written in UNIGUI to startup?

  • UniGUI Expiry Link Anonymizer

  • UniGUI Add CAPTCHA to the WEB project

  • UniGui. Unique hybrid of Delphi and JS

  • How to transform a blob field into string lines (varchar) in SQL Firebird?

CODE SAMPLESDATABASE
Home›CODE SAMPLES›How to add or remove hours from a SQL Firebird timestamp field

How to add or remove hours from a SQL Firebird timestamp field

By admin
January 13, 2021
579
0
Share:

Today we will see a SQL tip, very useful in our day to day.

There are cases that we are setting up our query, procedure or trigger and we need to change the value of a timestmp field, more precisely to add or remove hours.

the first thing that comes to mind, would be something like this

- to remove an hour, we normally think so, but that removes a day. 
SELECT 
CURRENT_TIMESTAMP  -  1 
FROM rdb $ database r

As stated in the comment, the sql above removes one day.

Then, using logic, we can reach the following conclusion:

 

-- add 12 hours.
SELECT
CURRENT_TIMESTAMP + 0.5
FROM rdb$database r
 
-- remove 6 hours.
SELECT
CURRENT_TIMESTAMP - 0.25
FROM rdb$database r
 
-- add 3 hours.
SELECT
CURRENT_TIMESTAMP + 0.125
FROM rdb$database r
 
--...

It even works, trying to calculate to find the corresponding value
1 – 24 hours
0.5 – 12 hours
0.25 – 6 hours
…

However, it is easy to make mistakes and it is difficult to read for someone else who will read the commands.

So, searching, I found the following UDF that comes as a standard when installing firebird.
ADDHOUR

As its name suggests, with it we can easily include hours and we will even use it to remove hours. Let’s take the examples:

To use it in general it is addhour (value_time_stamp, hours).

-- add 3 horus.
SELECT
addhour( CURRENT_TIMESTAMP, 3 )
FROM rdb$database r
 
- removing one hour 
SELECT 
addhour (  CURRENT_TIMESTAMP ,  - 1  ) 
FROM rdb $ database r

The secret to reducing hours was to pass the negative parameter.

There are other UDFs native to the installation that serve to add and remove minutes, seconds, days, etc. But the approach will be for a next post.

SQL queries were tested on Firebird 3.0.

They hope you enjoyed it guys.

Questions or suggestions? Leave your comment!

Good codes and see you next time. Thanks!



                                                            
Tagsdatabasefirebirdremove hourssqlsql firebirdtimestamp
Previous Article

How to extract text between HTML or ...

Next Article

How to transform a blob field into ...

0
Shares
  • 0
  • +
  • 0
  • 0
  • 0
  • 0

admin

Related articles More from author

  • CODE SAMPLESDELPHI

    Connect And Manage A SQLite Database For Delphi

    October 8, 2020
    By admin
  • CODE SAMPLESDATABASE

    How to transform a blob field into string lines (varchar) in SQL Firebird?

    January 13, 2021
    By admin
  • CODE SAMPLESTECH NEWS

    How to modify the maximum number of tcp connections on the windows server

    October 9, 2020
    By admin
  • CODE SAMPLESDELPHI

    Simple instructions for using uniGUI’s HyperServer (load balancing)

    October 11, 2020
    By admin
  • CODE SAMPLESDELPHI

    How to set up Unigui to run on Linux Operating System

    October 11, 2020
    By admin
  • CODE SAMPLESDELPHI

    Introduction to the properties of the delphi TForm class

    December 2, 2020
    By admin

Leave a reply Cancel reply

You may interested

  • CODE SAMPLESDELPHI

    Customize the appearance of uniGUI TreeMenu

  • CODE SAMPLESDELPHI

    How does Delphi handle different types of files

  • CODE SAMPLESDELPHI

    Delphi calculates MD5

  • LATEST REVIEWS

  • TOP REVIEWS

Timeline

  • January 27, 2021

    How to add a server application written in UNIGUI to startup?

  • January 27, 2021

    UniGUI Expiry Link Anonymizer

  • January 26, 2021

    UniGUI Add CAPTCHA to the WEB project

  • January 26, 2021

    UniGui. Unique hybrid of Delphi and JS

  • January 13, 2021

    How to transform a blob field into string lines (varchar) in SQL Firebird?

Latest Comments

Find us on Facebook

Follow Us on Instagram

logo

Our website, law, laws, copyright and aims to be respectful of individual rights. Our site, as defined in the law of 5651 serves as a provider of space. According to the law, contrary to the law of site management has no obligation to monitor content. Therefore, our site has adopted the principle of fit and remove. Works are subject to copyright laws and is shared in a manner that violated their legal rights, or professional associations, rights holders who, adsdelphi@gmail.com I can reach us at e-mail address. Complaints considered to be infringing on the examination of the content will be removed from our site.

About us

  • 660 Pennsylvania Avenue Southeast #100 Washington, DC 20003
  • 0123456789
  • adsdelphi@gmail.com
  • Recent

  • Popular

  • Comments

  • How to add a server application written in UNIGUI to startup?

    By admin
    January 27, 2021
  • UniGUI Expiry Link Anonymizer

    By admin
    January 27, 2021
  • UniGUI Add CAPTCHA to the WEB project

    By admin
    January 26, 2021
  • UniGui. Unique hybrid of Delphi and JS

    By admin
    January 26, 2021
  • How to add a server application written in UNIGUI to startup?

    By admin
    January 27, 2021
  • Amazing Cross Platform Email App Sample In Delphi 10.4.1 FireMonkey For Android And IOS

    By admin
    August 13, 2020
  • Critical Update Available For Delphi FireMonkey 10.4.1 On Android, IOS, OSX, Windows, And Linux

    By admin
    September 4, 2020
  • Setting up the IDE for your first Android Application in Delphi

    By admin
    September 7, 2020

Follow us

Find us on Facebook

© Copyright Delphifan Forum. All rights reserved.