Date and time functions- development blog

Nerd alert, not for all audiences

We have reworked all the date, time, and related functions inside the database to make use of the new c#-firebird engine. The advantage of this is that it prevents a lot of bugs and it allows us to use the full power of .net inside our database.

The following functions are available.

New or replaced functionalities

Cs_Age: returns today’s age
Cs_Destination_To_Local: converts a given timezone to the local timezone
Cs_Is_Leap_Year: returns if a year is a leap year
Cs_Local_To_TimeZone: converts a local time to a given timezone.
Cs_Local_To_Utc: converts a local time to universal time
Cs_Utc_Now: returns the current time in universal time
Cs_Utc_To_Local: converts a universal time to a local time
Cs_Utc_To_TimeZone: converts a universal time to a local time
Cs_TimeZone_To_Local: converts a given timezone to a local time
Cs_TimeZone_To_Utc: converts a given timezone to universal time
Cs_Format_DateTime: .net implementation on formatting dates and times. 

The default formatting of dates and times is configured by settings.  You can override them system wide with the settings under the group ‘FORMATS’.
‘DATE’, is now defaulted to “d”

“TIME”, is now defaulted to “T”

“DATETIME” is now defaulted to “G”

Interesting date and time functions

Sys_Begin_Of_FinDay: returns the beginning of a financial day
Sys_End_Of_FinDay: returns the end of a financial day
Sys_Begin_Of_WorkDay: returns the beginning of a workday
Sys_End_Of_WorkDay: returns the end of a workday
Sys_Current_TimeStamp: returns the current date and time of the local computer
Sys_Current_Center_TimeStamp: returns the current date and time, at the center
Sys_Current_TimeStamp_Utc: returns the current date and time in universal time
Sys_Current_Time: returns the current time of the local computer
Sys_Current_Time_Utc: returns the current time in universal time
Sys_Current_Center_Time: returns the current time at the center
Sys_Current_Date: returns the current date of the local computer
Sys_Current_Date_Utc: returns the current date in universal time
Sys_Current_Center_Date: returns the current date at the center
Nd_Center_Timezone: returns the timezone of the center
Nd_Server_Timezone: returns the timezone of the server
Nd_Local_Timezone: returns the local timezone as stored in the database
Cs_Local_Timezone: retrieves the timezone from the operating system

Sys_Dates_To_Period

Helper function to calculate ranges of dates

• In_From: Start date and time, when left zero, current date is taken.
• In_To: can be omitted, end of the period
• In_Mode: decides what should be calculated
  • WorkDays: converts the in_from and in_to to workdays
  • CurrentMonth: converts the in_from to begin – end of the month
  • LastMonth: converts the in_from to begin – end of the previous month
  • NextMonth: converts the in_from to begin – end of the next month
  • CurrentWeek: converts the in_from to begin – end of the current week
  • LastWeek: converts the in_from to begin – end of the previous week
  • NextWeek: converts the in_from to begin – end of the next week
  • CurrentYear: converts the in_from to begin – end of the current year
  • LastYear: converts the in_from to begin – end of the previous year
  • NextYear: converts the in_from to begin – end of the next year
  • YearToDate: 01-01-XXXX – until in from
• In_Day_Mode: decides how a day should be calculated
  • WorkDay: Normal operation hours
  • FinDay: Accountancy operation hours

caveats with “now”, “current_timestamp”, “cs_utc_now”…

It is important to know a few differences between ways of getting dates and times.

The most common function used in our database is current_timestamp.  The current timestamp is the time when the transaction has started.  It stays stable during the whole transaction.  If your transaction takes one hour, this value will be off with one hour.

Sadly, if you use current_timestamp is it impossible for us to write automated tests since we need to be able to play around with time in order to test things. We have provided an alternative and that is called “Sys_Current_Timestamp / Sys_Current_Timestamp_Utc”.  It can be used in almost the same way with that difference that we can override it and use it for unit tests.  Sys_Current_Timestamp is also stable during the transaction.

“Now” and “Cs_Utc_Now” are not stable they will give you the most accurate date and time available.  Beware, this is not always better.

Deprecated functions

The following functions are no longer available inside the database

Age, Format_Date, Format_DateTime, Format_Time, Format_TimeStamp, ToUtc, FromUtc