Upgrade to 4.0.8 with mod of ExternalAuth + absolute paths to ticket-menu.
[usit-rt.git] / docs / customizing / timezones_in_charts.pod
CommitLineData
84fb5b46
MKG
1=head1 INTRODUCTION
2
3Every date in RT's DB is stored in UTC format. This affects charts
4grouped by time periods (Annually, Monthly, etc.), in that they are by
5default shown in UTC. To produce charts that are in a specific timezone,
6we have to use database-specific functions to convert between timezones;
7unsurprisingly, each DB has very different requirements.
8
9=head1 CONFIGURATION
10
11This code is experimental; you can enable it using the
12C<$ChartsTimezonesInDB> configuration option.
13
14=head1 DATABASE SPECIFIC NOTES
15
16=head2 mysql
17
18The time adjustment cannot simply be converted using a numeric time
19shift, as this shift value depends on the daylight saving time
20properties of the time zone.
21
22mysql since 4.1.3 supports named timezones, but you have to fill special
23tables with up-to-date timezone data. On modern systems, this is usually
24a simple case of:
25
26 mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
27
28mysql's doc recommends you restart server after running this; you can
29read more about mysql's timezone support at
30L<http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html>
31
32=head2 PostgreSQL
33
34PostgreSQL uses your operating system's functions to convert timezones.
35Thus, you don't need to do anything in particular except to make sure
36that the data in F</usr/share/zoneinfo> is up to date. On some systems
37this may mean upgrading a system package.
38
39=head3 Note for users of Pg 7.2 and older or users upgraded from those
40
41You should be sure that timestamps in RT DB have no TZ set. The
42TIMESTAMP column type in PostgreSQL prior to Pg 7.3 had timezone info by
43default; this has been removed in more recent versions. If your RT
44database has this embedded timezone info, you will need to alter the
45columns to remove them before enabling this feature.
46
47=head2 Other databases
48
49There is no implementation for Oracle or SQLite at current.
50
51=head1 FOR DEVELOPERS
52
53=head2 PostgreSQL
54
55We use the timestamp type for all datetime fields. It either has
56timezone info or not, since by default Pg 7.3 and above have no
57timezone. Conversion is kinda tricky:
58
59 timezone('Europe/Moscow', timezone('UTC', column_without_tz_info))
60 timezone('to_tz', timezone('from_tz', column_without_tz_info))
61
62This function flips the HAS_TZ flag on the argument, and moves the
63timestamp to UTC. The first call makes no conversion, but flips the
64HAS_TZ flag; the second call flips it back and does actual conversion.
65
66For more information, See
67L<http://www.postgresql.org/docs/7.4/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT>
68and
69L<http://www.postgresql.org/docs/7.4/static/datatype-datetime.html#DATATYPE-TIMEZONES>
70
71=head2 mysql
72
73Once timezone information is loaded into tables on the server,
74we have all the same set of named timezones in the system
75and DateTime (DateTime project has copy of the TZ data in a module).
76
77CONVERT_TZ(TS, from, to) exists since mysql 4.1.3. Note that it takes a
78timestamp, so it only supports limitted date range (usuall 1970-2038).
79
80=head2 Oracle
81
82Look at FROM_TZ function.
83
84=head2 SQLite
85
86Has no apparent timezone support.
87
88=cut