1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
=head1 INTRO
Every date in RT's DB is stored in UTC format. This affects charts
grouped by time periods (Annually, Monthly, etc.). To produce
charts that are in a specific timezone we have to use database specific
functions to convert time. Each DB has very different requirements.
=head1 CONFIGURATION
This code is experimental and you can turn it on and off using
boolean option $ChartsTimezonesInDB in the RT config.
=head1 DATABASE SPECIFIC NOTES
=head2 mysql
Time can not just be converted using numeric time shift as this
shift value depends on daylight saving time properties of the time zone.
mysql since 4.1.3 supports named timezones, but you have to fill
special tables with up to date data. On modern systems it's Usually
very easy:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
mysql's doc recommends to restart server. Read more about timezones
in mysql in the following document
http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html .
=head2 PostgreSQL
PostgreSQL database uses your operating system's functions to convert
timezones. You don't need to do anything in particular except making
sure that the data in F</usr/share/zoneinfo> is up to date. On some
systems this means upgrading a system package.
=head3 Note for users of Pg 7.2 and older or users upgraded from those
You should be sure that timestamps in RT DB have no TZ set. TIMESTAMP
column type in PostgreSQL prior to Pg 7.3 has timezone info by default.
In newer versions it's not the case anymore. If your RT database has
this embedded timezone info, you need to alter the columns before
enabling this feature.
=head2 Other databases
There is no implementation for other DBs, yet.
=head1 FOR DEVELOPERS
=head2 PostgreSQL
We use timestamp type for all datetime fields. It either has timezone
info or not, by default since Pg 7.3 it has no timezone. Conversion is
kinda tricky:
timezone('Europe/Moscow', timezone('UTC', column_without_tz_info))
timezone('to_tz', timezone('from_tz', column_without_tz_info))
http://www.postgresql.org/docs/7.4/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
This function flips HAS_TZ flag on the argument. First call makes
no conversion, but flips HAS_TZ flag. So next call flips it back
and does actual conversion.
http://www.postgresql.org/docs/7.4/static/datatype-datetime.html#DATATYPE-TIMEZONES
=head2 mysql
Once timezone information is loaded into tables on the server,
we have all the same set of named timezones in the system
and DateTime (DateTime project has copy of the TZ data in a module).
CONVERT_TZ(TS, from, to) exists since mysql 4.1.3. Note that it
takes timestamp, so supports limitted range (usuall 1970-2038).
=head2 Oracle
Look at FROM_TZ function.
=head2 SQLite
As far as I can see has no support.
=cut
|
