In Firebird 2.5, how are TIME and TIMESTAMP data types stored in the database?
It seems to me that they are not stored in UTC, but rather as the value which the user gave the DB and as such will always display that value when retrieved from the DB irrespective of the timezone of the computer. Is that correct?
Side note: I know about the TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE data types in Firebird 4.0 and higher, but was wondering about the traditional ones.
In Firebird 2.5, how are TIME and TIMESTAMP data types stored in the database?
It seems to me that they are not stored in UTC, but rather as the value which the user gave the DB and as such will always display that value when retrieved from the DB irrespective of the timezone of the computer. Is that correct?
Side note: I know about the TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE data types in Firebird 4.0 and higher, but was wondering about the traditional ones.
Timestamp type is stored as two integers. The first is the number of days since November 17, 1858, and the second is the number of 10,000ths of a second (100 microseconds) since midnight.
Time is only the latter.
There is no place to store the time zone, so these numbers are stored as specified by the user and retrieved as is.
Firebird 3.0 and older have no timezone support at all. So the value is stored exactly as provided, and functions like LOCALTIMESTAMP/CURRENT_TIMESTAMP and LOCALTIME/CURRENT_TIME provide the local time in the timezone of the server.
As user13964273 already answered, a TIMESTAMP consists of a DATE component and a TIME component. The DATE is the number of days since November 17, 1858 (a.k.a. a Modified Julian Date or MJD). The TIME component is the number of "fractions" since midnight, where a "fraction" is 100 microseconds.
These are all without any timezone reference, so basically what is often called a "local" date and time, and only you (or your program) determines its interpretation (e.g. relative to what timezone), or you need an additional column so your programs can store the intended timezone.
If you want it to be UTC, you must configure your Firebird server in the UTC timezone, and you need to ensure all your clients only store UTC time values, and interpret queried values as UTC.
