0
# Timezone Support
1

2
Timezone handling utilities for PostgreSQL timestamp types with timezone information, including fixed offset and local timezone support for proper datetime handling across different time zones.
3

4
## Capabilities
5

6
### Fixed Offset Timezone
7

8
Timezone implementation for fixed UTC offsets, useful for handling timestamps with known timezone offsets.
9

10
```python { .api }
11
class FixedOffsetTimezone(datetime.tzinfo):
12
    """Fixed UTC offset timezone."""
13
    
14
    def __init__(self, offset=None, name=None):
15
        """
16
        Initialize fixed offset timezone.
17
        
18
        Parameters:
19
        - offset (timedelta, optional): UTC offset (default: UTC)
20
        - name (str, optional): Timezone name
21
        """
22
    
23
    def utcoffset(self, dt):
24
        """
25
        Return UTC offset.
26
        
27
        Parameters:
28
        - dt (datetime): Datetime object
29
        
30
        Returns:
31
        timedelta: UTC offset
32
        """
33
    
34
    def tzname(self, dt):
35
        """
36
        Return timezone name.
37
        
38
        Parameters:
39
        - dt (datetime): Datetime object
40
        
41
        Returns:
42
        str: Timezone name
43
        """
44
    
45
    def dst(self, dt):
46
        """
47
        Return DST offset (always None for fixed offset).
48
        
49
        Parameters:
50
        - dt (datetime): Datetime object
51
        
52
        Returns:
53
        None: No DST for fixed offset
54
        """
55
```
56

57
**Usage Example:**
58

59
```python
60
import psycopg2
61
from psycopg2.tz import FixedOffsetTimezone
62
from datetime import datetime, timedelta
63

64
# Create fixed offset timezones
65
utc = FixedOffsetTimezone()  # UTC (no offset)
66
est = FixedOffsetTimezone(timedelta(hours=-5), "EST")  # Eastern Standard Time
67
pst = FixedOffsetTimezone(timedelta(hours=-8), "PST")  # Pacific Standard Time
68
cet = FixedOffsetTimezone(timedelta(hours=1), "CET")   # Central European Time
69

70
# Create timezone-aware datetime objects
71
utc_time = datetime(2023, 12, 25, 12, 0, 0, tzinfo=utc)
72
est_time = datetime(2023, 12, 25, 7, 0, 0, tzinfo=est)  # Same as UTC time
73
pst_time = datetime(2023, 12, 25, 4, 0, 0, tzinfo=pst)  # Same as UTC time
74

75
print(f"UTC: {utc_time}")
76
print(f"EST: {est_time}")
77
print(f"PST: {pst_time}")
78

79
# Store timezone-aware timestamps in PostgreSQL
80
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
81

82
with conn.cursor() as cur:
83
    # Insert timezone-aware timestamps
84
    cur.execute(
85
        "INSERT INTO events (name, created_at, timezone) VALUES (%s, %s, %s)",
86
        ("Christmas UTC", utc_time, "UTC")
87
    )
88
    
89
    cur.execute(
90
        "INSERT INTO events (name, created_at, timezone) VALUES (%s, %s, %s)",
91
        ("Christmas EST", est_time, "EST")
92
    )
93
    
94
    cur.execute(
95
        "INSERT INTO events (name, created_at, timezone) VALUES (%s, %s, %s)",
96
        ("Christmas PST", pst_time, "PST")
97
    )
98
    
99
    # Query timezone-aware data
100
    cur.execute("SELECT name, created_at, timezone FROM events ORDER BY created_at")
101
    
102
    for name, created_at, tz_name in cur.fetchall():
103
        print(f"{name}: {created_at} ({tz_name})")
104
        print(f"  Timezone: {created_at.tzinfo}")
105
        print(f"  UTC Offset: {created_at.utcoffset()}")
106

107
conn.commit()
108
conn.close()
109
```
110

111
### Local Timezone
112

113
Platform-specific local timezone implementation that automatically detects the system's local timezone settings.
114

115
```python { .api }
116
class LocalTimezone(datetime.tzinfo):
117
    """Platform's local timezone."""
118
    
119
    def utcoffset(self, dt):
120
        """
121
        Return local UTC offset.
122
        
123
        Parameters:
124
        - dt (datetime): Datetime object
125
        
126
        Returns:
127
        timedelta: Local UTC offset
128
        """
129
    
130
    def tzname(self, dt):
131
        """
132
        Return local timezone name.
133
        
134
        Parameters:
135
        - dt (datetime): Datetime object
136
        
137
        Returns:
138
        str: Local timezone name
139
        """
140
    
141
    def dst(self, dt):
142
        """
143
        Return DST offset if applicable.
144
        
145
        Parameters:
146
        - dt (datetime): Datetime object
147
        
148
        Returns:
149
        timedelta: DST offset or None
150
        """
151
```
152

153
**Usage Example:**
154

155
```python
156
import psycopg2
157
from psycopg2.tz import LocalTimezone, LOCAL
158
from datetime import datetime
159

160
# Use local timezone
161
local_tz = LocalTimezone()
162
# Or use the pre-created instance
163
local_tz = LOCAL
164

165
# Create datetime with local timezone
166
local_time = datetime.now(local_tz)
167
print(f"Local time: {local_time}")
168
print(f"Local timezone: {local_time.tzname()}")
169
print(f"UTC offset: {local_time.utcoffset()}")
170
print(f"DST offset: {local_time.dst()}")
171

172
# Compare with naive datetime
173
naive_time = datetime.now()
174
print(f"Naive time: {naive_time}")
175
print(f"Timezone info: {naive_time.tzinfo}")
176

177
# Store local time in database
178
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
179

180
with conn.cursor() as cur:
181
    # Insert with local timezone
182
    cur.execute(
183
        "INSERT INTO logs (message, timestamp) VALUES (%s, %s)",
184
        ("Application started", local_time)
185
    )
186
    
187
    # Query back with timezone preservation
188
    cur.execute("SELECT message, timestamp FROM logs WHERE message = %s", 
189
                ("Application started",))
190
    
191
    message, timestamp = cur.fetchone()
192
    print(f"Retrieved: {message} at {timestamp}")
193
    print(f"Retrieved timezone: {timestamp.tzinfo}")
194

195
conn.commit()
196
conn.close()
197
```
198

199
### Timezone Constants
200

201
Pre-defined timezone instances and utility constants for common timezone operations.
202

203
```python { .api }
204
LOCAL: LocalTimezone  # Local timezone instance
205
ZERO: timedelta       # Zero timedelta constant
206
```
207

208
**Usage Example:**
209

210
```python
211
from psycopg2.tz import LOCAL, ZERO, FixedOffsetTimezone
212
from datetime import datetime, timedelta
213

214
# Use pre-defined LOCAL timezone
215
current_time = datetime.now(LOCAL)
216
print(f"Current local time: {current_time}")
217

218
# Use ZERO constant for calculations
219
utc_tz = FixedOffsetTimezone(ZERO, "UTC")
220
utc_time = datetime.now(utc_tz)
221

222
# Calculate time differences
223
time_diff = current_time.utcoffset() or ZERO
224
print(f"Local offset from UTC: {time_diff}")
225

226
# Create timezone-aware datetime for different scenarios
227
def create_timezone_aware_datetime(year, month, day, hour=0, minute=0, second=0, use_local=True):
228
    """Create timezone-aware datetime."""
229
    if use_local:
230
        return datetime(year, month, day, hour, minute, second, tzinfo=LOCAL)
231
    else:
232
        return datetime(year, month, day, hour, minute, second, tzinfo=FixedOffsetTimezone(ZERO))
233

234
# Usage examples
235
local_new_year = create_timezone_aware_datetime(2024, 1, 1, 0, 0, 0, use_local=True)
236
utc_new_year = create_timezone_aware_datetime(2024, 1, 1, 0, 0, 0, use_local=False)
237

238
print(f"Local New Year: {local_new_year}")
239
print(f"UTC New Year: {utc_new_year}")
240
```
241

242
### Timezone Conversion and Operations
243

244
Working with timezone conversions and datetime arithmetic across different timezones.
245

246
**Usage Example:**
247

248
```python
249
import psycopg2
250
from psycopg2.tz import FixedOffsetTimezone, LOCAL, ZERO
251
from datetime import datetime, timedelta
252

253
# Create various timezone objects
254
utc = FixedOffsetTimezone(ZERO, "UTC")
255
tokyo = FixedOffsetTimezone(timedelta(hours=9), "JST")  # Japan Standard Time
256
london = FixedOffsetTimezone(timedelta(hours=0), "GMT")  # Greenwich Mean Time
257
new_york = FixedOffsetTimezone(timedelta(hours=-5), "EST")  # Eastern Standard Time
258

259
# Create a datetime in one timezone
260
utc_datetime = datetime(2023, 12, 25, 15, 30, 0, tzinfo=utc)
261

262
# Convert to different timezones (manual conversion)
263
def convert_timezone(dt, new_tz):
264
    """Convert datetime to different timezone."""
265
    # Convert to UTC first, then to target timezone
266
    utc_dt = dt.replace(tzinfo=utc) if dt.tzinfo is None else dt
267
    utc_timestamp = utc_dt.utctimetuple()
268
    
269
    # Create new datetime in target timezone
270
    naive_dt = datetime(*utc_timestamp[:6])
271
    target_offset = new_tz.utcoffset(naive_dt)
272
    target_dt = naive_dt + target_offset
273
    
274
    return target_dt.replace(tzinfo=new_tz)
275

276
# Store meeting times across multiple timezones
277
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
278

279
meeting_time_utc = datetime(2023, 12, 25, 14, 0, 0, tzinfo=utc)
280

281
with conn.cursor() as cur:
282
    # Store meeting in different timezone contexts
283
    timezones = [
284
        (utc, "UTC"),
285
        (tokyo, "Tokyo"),
286
        (london, "London"), 
287
        (new_york, "New York")
288
    ]
289
    
290
    for tz, city in timezones:
291
        # Convert meeting time to local time for each city
292
        if tz == utc:
293
            local_time = meeting_time_utc
294
        else:
295
            # Simple offset calculation for demonstration
296
            offset = tz.utcoffset(meeting_time_utc)
297
            local_time = meeting_time_utc.replace(tzinfo=None) + offset
298
            local_time = local_time.replace(tzinfo=tz)
299
        
300
        cur.execute(
301
            "INSERT INTO meetings (title, start_time, timezone, city) VALUES (%s, %s, %s, %s)",
302
            ("Global Team Meeting", local_time, tz.tzname(local_time), city)
303
        )
304
        
305
        print(f"{city}: {local_time} ({tz.tzname(local_time)})")
306
    
307
    # Query meetings with timezone information
308
    cur.execute("""
309
        SELECT title, start_time, timezone, city 
310
        FROM meetings 
311
        WHERE title = %s 
312
        ORDER BY start_time
313
    """, ("Global Team Meeting",))
314
    
315
    print("\nMeeting times by city:")
316
    for title, start_time, timezone, city in cur.fetchall():
317
        print(f"{city}: {start_time} {timezone}")
318

319
conn.commit()
320
conn.close()
321
```
322

323
### PostgreSQL Timezone Integration
324

325
Best practices for working with PostgreSQL's timezone-aware data types.
326

327
**Usage Example:**
328

329
```python
330
import psycopg2
331
from psycopg2.tz import FixedOffsetTimezone, LOCAL
332
from datetime import datetime, timedelta
333

334
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
335

336
# Create table with timezone-aware columns
337
with conn.cursor() as cur:
338
    cur.execute("""
339
        CREATE TABLE IF NOT EXISTS user_activities (
340
            id SERIAL PRIMARY KEY,
341
            user_id INTEGER,
342
            activity_type VARCHAR(50),
343
            created_at TIMESTAMP WITH TIME ZONE,
344
            scheduled_at TIMESTAMP WITH TIME ZONE,
345
            local_time TIMESTAMP WITHOUT TIME ZONE
346
        )
347
    """)
348

349
# Insert timezone-aware data
350
utc = FixedOffsetTimezone(timedelta(0), "UTC")
351
pst = FixedOffsetTimezone(timedelta(hours=-8), "PST")
352

353
with conn.cursor() as cur:
354
    # Insert with different timezone contexts
355
    activities = [
356
        (1, "login", datetime.now(utc), None, datetime.now()),
357
        (2, "logout", datetime.now(pst), None, datetime.now()),
358
        (1, "scheduled_task", datetime.now(LOCAL), 
359
         datetime.now(utc) + timedelta(hours=2), datetime.now())
360
    ]
361
    
362
    for user_id, activity, created_at, scheduled_at, local_time in activities:
363
        cur.execute("""
364
            INSERT INTO user_activities 
365
            (user_id, activity_type, created_at, scheduled_at, local_time)
366
            VALUES (%s, %s, %s, %s, %s)
367
        """, (user_id, activity, created_at, scheduled_at, local_time))
368
    
369
    # Query with timezone functions
370
    cur.execute("""
371
        SELECT 
372
            activity_type,
373
            created_at,
374
            created_at AT TIME ZONE 'UTC' as utc_time,
375
            created_at AT TIME ZONE 'America/Los_Angeles' as pst_time,
376
            extract(timezone from created_at) as tz_offset_seconds
377
        FROM user_activities
378
        ORDER BY created_at
379
    """)
380
    
381
    print("Activity timeline with timezone conversions:")
382
    for activity, created_at, utc_time, pst_time, tz_offset in cur.fetchall():
383
        print(f"{activity}:")
384
        print(f"  Original: {created_at}")
385
        print(f"  UTC: {utc_time}")
386
        print(f"  PST: {pst_time}")
387
        print(f"  TZ Offset: {tz_offset} seconds")
388
        print()
389

390
conn.commit()
391
conn.close()
392
```
393

394
## Types
395

396
### Timezone Class Interface
397

398
```python { .api }
399
class FixedOffsetTimezone(datetime.tzinfo):
400
    """Fixed UTC offset timezone."""
401
    
402
    def __init__(self, offset: timedelta | None = None, name: str | None = None) -> None:
403
        """Initialize with offset and name."""
404
    
405
    def utcoffset(self, dt: datetime) -> timedelta:
406
        """Return UTC offset."""
407
    
408
    def tzname(self, dt: datetime) -> str:
409
        """Return timezone name."""
410
    
411
    def dst(self, dt: datetime) -> None:
412
        """Return DST offset (always None)."""
413

414
class LocalTimezone(datetime.tzinfo):
415
    """Platform local timezone."""
416
    
417
    def utcoffset(self, dt: datetime) -> timedelta:
418
        """Return local UTC offset."""
419
    
420
    def tzname(self, dt: datetime) -> str:
421
        """Return local timezone name."""
422
    
423
    def dst(self, dt: datetime) -> timedelta | None:
424
        """Return DST offset if applicable."""
425
```
426

427
### Timezone Constants
428

429
```python { .api }
430
LOCAL: LocalTimezone  # Pre-created local timezone instance
431
ZERO: timedelta      # Zero timedelta (timedelta(0))
432
```
433

434
### Common Timezone Offsets
435

436
```python { .api }
437
# Common timezone offsets (examples)
438
UTC_OFFSET = timedelta(0)           # UTC
439
EST_OFFSET = timedelta(hours=-5)    # Eastern Standard Time
440
PST_OFFSET = timedelta(hours=-8)    # Pacific Standard Time  
441
CET_OFFSET = timedelta(hours=1)     # Central European Time
442
JST_OFFSET = timedelta(hours=9)     # Japan Standard Time
443
```