meta data for this page
  •  

Date/Time Functions

139 functions and 27 variants

Preliminary note

Calculate Functions - 11 functions

Determine Functions - 78 functions and 23 variants

Format Functions - 16 functions and 4 variants

Compare Functions - 4 functions

Convert Functions - 7 functions

Movable Feasts Functions - 14 functions

doesn't run with InterBase

Firebird 2.0 - returns <null> instead of 0, '' (empty string) or '17.11.1858'

Interbase 6.0 - since InterBase 6.0 this function is substitutable with a native SQL statement

Firebird 1.0 - since FireBird 1.0 this function is substitutable with a native SQL statement

Firebird 2.1 - since FireBird 2.1 this function is substitutable with a native SQL statement

Output RETURN mechanism if nothing other is published: FREE_IT TestSQLs with NULL run only in FireBird 2.

Date-time functions: Preliminary note

For the calculation of time, there are used different dates. The most important are

  • Julian Date (JD)
  • modified Julian Date (MJD)
  • Dubliner Julian Date (DJD)
  • ANSI Date
  • UNIX Time (POSIX-Standard)

The Julian Date (JD) is a continuous count of days and fractions elapsed since 1st January -4712 12:00. For example the 1st Januar 2000 12:00 is the julian date 2.451.545,0.

The julian date did'nt mistake with a date in the julian calendar.

As a continious count of days the julian date is free of irregularities like leap days, different length of month a.s.o. you can find in most of calendars. It is used mostly in astronomy to describe time-dependents for easy calculation of time-differences.

In international geophysical year (1957/1958) a modified Julian Date (mJD oder MJD) was established with zeropoint at 1985-11-17 0:00 UT(worldtime): MJD = JD – 2.400.000,5. It was first used to record the orbit of Sputnik via an IBM 704 (36-bit machine). Digital Equipment Corporation (DEC) used MJD in their OS VMS and their database Rdb/VMS, on which Jim Starkey cooperated before he leaves DEC and programmed his own RDBMS (relational database management system) (Groton DataBase), which becomes InterBase.

MJD is mainly used in geodesy, geophysics and space flight, but not become accepted in astronomy.

The Dublin Julian Date (DJD) is another version of the Julian Date. The count of days started with the beginning of the year 1900 (used in Microsoft Excel, Lotus-123, Delphi) or the the year 1904 (Microsoft Excel for Mac OS). Because the count starts on 1st Januar with 1, the correct zeropoint is 1899-12-31 0:00. Some programms make more confusion because they use 1900 as a leap year and therefore their dates after 28th february 1900 are wrong (zeropoint form them is 1899-12-30 0:00).

The ANSI-Date starts with 1st January 1601 as day „1“. It's used in COBOL.

Unix time, or POSIX time, is a system for describing points in time: it is the number of seconds elapsed since midnight UTC of January 1, 1970, not counting leap seconds. This starting-time is called The EPOCH. It is widely used not only on Unix-like operating systems but also in many other computing systems.

(look http://en.wikipedia.org/wiki/Julian_date and http://en.wikipedia.org/wiki/Unix_time)

InterBase and FireBird uses the modified Julian Date with 1858-11-17 as day 0.