Differences between CHAR and VARCHAR

Many people believe that VARCHAR is better because it stores only actual data, while CHAR is stored in full length. It is not true. In fact, both CHAR and VARCHAR are stored in memory buffer in their full, declared length; when the row is stored on disk, then RLE compression algorithm is used to compress whole row, i.e. CHARs, VARCHARs, INTEGERs, DATEs, etc. all together. So if you want to save space, CHARs are slightly better than VARCHARs (the differenece is that VARCHAR stores string length in two bytes).
There is also a bug that causes that VARCHAR does not properly clean string tail if you assign shorter string, thus causing worse compression. (this problem is fixed in Firebird-0.9.4)

Many people also believe that VARCHAR sends over network only actual data, while CHAR is sent in full length. It is not true either. Communication between client and server is done via messages of fixed length. For this reason both CHAR and VARCHAR are sent in their full declared length. (this problem is fixed in IB-6.5)

So decision whether use CHAR or VARCHAR should be based solely on application's requirements. E.g. store fixed length codes in CHAR, store names in VARCHAR (to allow correct concatenating).

Date and Time Calculations (Firebird)

Date and Time Calculations
When doing arithmetic operations with TIMESTAMP values, Firebird works with them as decimal number, where

* the integral fraction is the number of days
* the decimal fraction is the part of a day

For example:

* Two days: 2.0
* One hour: 1.0/24.0
* One minute: 1.0/1440.0
* One second: 1.0/86400.0

Extracting the smaller units from a TIMESTAMP value:

* Number of seconds: VALUE*86400.0
* Number of minutes: VALUE*1440.0
* Number of hours: VALUE*24.0

Don't forget the decimal point in the numbers (e.g. 1.0), otherwise the result will be integer. I spent a lot time debugging a calculation that didn't work because I was dividing by an integer. Since that time, I remember.
Posted by Dan : 2:04 PM


---------------
firebird:
tgl jam server sekarang : current_timestamp
tgl : CAST ('today' AS TIMESTAMP)
jam : extract(hour from current_timestamp)||':'||extract(minute from current_timestamp)||':'||extract(second from current_timestamp)