17.4. Date, Time, and Time Zones

Make sure all columns in PostERP database use PostgreSQL's data typeTIMESTAMP WITH TIME ZONE for date time values rather than using date type TIMESTAMP WITHOUT TIME ZONE.

Refer to user manual regarding Chapter 8date, time, time zones for more information.

PostERP supports true single database, multiple companies, and multiple time zones.

For example, being a PostERP customer, you use only one server running PostgreSQL in Taiwan. This customer creates only one database, posterp, in this server, then this one database instance posterp automatically suports multiple companies like this:

Database posterp simultaneously supports company A and B. Company A is located in Taiwan in time zone UTC+8; CompanyB is located in Japan in time zone UTC+9.

Now you want to design a report template for all companies and all end users. This report template will accept end users' inputs for year and month and the SQL will then print all Journal# after filtering the general journal rows whose Transaction Dates fall in the year and month specified by end users. For the associated SQL to work, you can use the two pairs of PostgreSQL functions provided in PostERP:

1. MonthBegin(year INTEGER,month INTEGER,company TEXT) and MonthBegin(PresentTime TIMESTAMPTZ,company TEXT)

2. MonthEnd(year INTEGER,month INTEGER,company TEXT) and MonthEnd(PresentTime TIMESTAMPTZ,company TEXT)

You will design the SQL as such:

SELECT f1 FROM t52 WHERE f0=:com AND f2 BETWEEN MonthBegin(:year,:month,:com) AND MonthEnd(:year,:month,:com)
		

and then specify the purposes of these SQL:

• com: to accept end users's input for company#

• year: to accept end users's input for year

• month: to accept end users's input for month

Here we also assume that the target company is located in Japan, and users around the world will enter year 2012 and month 7 and then run this report. Provided with users' input, this SQL will select journal rows whose Transaction Date Time falling between 2012-7-1 00:00:00+9 and 2012-7-31 23:59:59.99+9 regardless of the time zones the end user might be in. Such designed report template will work for all companies in all possible time zones for world end users.

Functions MonthBegin(PresentTime TIMESTAMPTZ,company TEXT) and MonthBegin(PresentTime TIMESTAMPTZ,company TEXT) are similar to the aforementioned counterparts. Assuming an end user is in Japan whose local time is now 2012-07-08 15:20:40+09 and (s)he is manipulating the data of a Taiwan company, then

• MonthBegin(NOW(),:com) returns 2012-07-01 00:00:00+08

• MonthEnd(NOW(),:com) returns 2012-07-31 23:59:59.99+08

PostERP realize the goal of single design, used by multiple companies and users located in multiple time zones because

• it provides convenient interface via thin client to end users with time zone-abled date time values, and

• it provides PostERP developers with handy PostgreSQL functions returning TIMESTAMPTZ. With these functions, developers do not have to worry about the issue of time zones conversion. They simply use them in functions, report templates, data quick view and get the jobs done.