tessl/pypi-psycopg2

Python-PostgreSQL Database Adapter

—

Pending

Overview

Eval results

Files

Timezone Support

Name: tessl/pypi-psycopg2
Author: tessl

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.

Capabilities

Fixed Offset Timezone

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

class FixedOffsetTimezone(datetime.tzinfo):
    """Fixed UTC offset timezone."""
    
    def __init__(self, offset=None, name=None):
        """
        Initialize fixed offset timezone.
        
        Parameters:
        - offset (timedelta, optional): UTC offset (default: UTC)
        - name (str, optional): Timezone name
        """
    
    def utcoffset(self, dt):
        """
        Return UTC offset.
        
        Parameters:
        - dt (datetime): Datetime object
        
        Returns:
        timedelta: UTC offset
        """
    
    def tzname(self, dt):
        """
        Return timezone name.
        
        Parameters:
        - dt (datetime): Datetime object
        
        Returns:
        str: Timezone name
        """
    
    def dst(self, dt):
        """
        Return DST offset (always None for fixed offset).
        
        Parameters:
        - dt (datetime): Datetime object
        
        Returns:
        None: No DST for fixed offset
        """

Usage Example:

import psycopg2
from psycopg2.tz import FixedOffsetTimezone
from datetime import datetime, timedelta

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

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

print(f"UTC: {utc_time}")
print(f"EST: {est_time}")
print(f"PST: {pst_time}")

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

with conn.cursor() as cur:
    # Insert timezone-aware timestamps
    cur.execute(
        "INSERT INTO events (name, created_at, timezone) VALUES (%s, %s, %s)",
        ("Christmas UTC", utc_time, "UTC")
    )
    
    cur.execute(
        "INSERT INTO events (name, created_at, timezone) VALUES (%s, %s, %s)",
        ("Christmas EST", est_time, "EST")
    )
    
    cur.execute(
        "INSERT INTO events (name, created_at, timezone) VALUES (%s, %s, %s)",
        ("Christmas PST", pst_time, "PST")
    )
    
    # Query timezone-aware data
    cur.execute("SELECT name, created_at, timezone FROM events ORDER BY created_at")
    
    for name, created_at, tz_name in cur.fetchall():
        print(f"{name}: {created_at} ({tz_name})")
        print(f"  Timezone: {created_at.tzinfo}")
        print(f"  UTC Offset: {created_at.utcoffset()}")

conn.commit()
conn.close()

Local Timezone

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

class LocalTimezone(datetime.tzinfo):
    """Platform's local timezone."""
    
    def utcoffset(self, dt):
        """
        Return local UTC offset.
        
        Parameters:
        - dt (datetime): Datetime object
        
        Returns:
        timedelta: Local UTC offset
        """
    
    def tzname(self, dt):
        """
        Return local timezone name.
        
        Parameters:
        - dt (datetime): Datetime object
        
        Returns:
        str: Local timezone name
        """
    
    def dst(self, dt):
        """
        Return DST offset if applicable.
        
        Parameters:
        - dt (datetime): Datetime object
        
        Returns:
        timedelta: DST offset or None
        """

Usage Example:

import psycopg2
from psycopg2.tz import LocalTimezone, LOCAL
from datetime import datetime

# Use local timezone
local_tz = LocalTimezone()
# Or use the pre-created instance
local_tz = LOCAL

# Create datetime with local timezone
local_time = datetime.now(local_tz)
print(f"Local time: {local_time}")
print(f"Local timezone: {local_time.tzname()}")
print(f"UTC offset: {local_time.utcoffset()}")
print(f"DST offset: {local_time.dst()}")

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

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

with conn.cursor() as cur:
    # Insert with local timezone
    cur.execute(
        "INSERT INTO logs (message, timestamp) VALUES (%s, %s)",
        ("Application started", local_time)
    )
    
    # Query back with timezone preservation
    cur.execute("SELECT message, timestamp FROM logs WHERE message = %s", 
                ("Application started",))
    
    message, timestamp = cur.fetchone()
    print(f"Retrieved: {message} at {timestamp}")
    print(f"Retrieved timezone: {timestamp.tzinfo}")

conn.commit()
conn.close()

Timezone Constants

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

LOCAL: LocalTimezone  # Local timezone instance
ZERO: timedelta       # Zero timedelta constant

Usage Example:

from psycopg2.tz import LOCAL, ZERO, FixedOffsetTimezone
from datetime import datetime, timedelta

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

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

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

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

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

print(f"Local New Year: {local_new_year}")
print(f"UTC New Year: {utc_new_year}")

Timezone Conversion and Operations

Working with timezone conversions and datetime arithmetic across different timezones.

Usage Example:

import psycopg2
from psycopg2.tz import FixedOffsetTimezone, LOCAL, ZERO
from datetime import datetime, timedelta

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

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

# Convert to different timezones (manual conversion)
def convert_timezone(dt, new_tz):
    """Convert datetime to different timezone."""
    # Convert to UTC first, then to target timezone
    utc_dt = dt.replace(tzinfo=utc) if dt.tzinfo is None else dt
    utc_timestamp = utc_dt.utctimetuple()
    
    # Create new datetime in target timezone
    naive_dt = datetime(*utc_timestamp[:6])
    target_offset = new_tz.utcoffset(naive_dt)
    target_dt = naive_dt + target_offset
    
    return target_dt.replace(tzinfo=new_tz)

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

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

with conn.cursor() as cur:
    # Store meeting in different timezone contexts
    timezones = [
        (utc, "UTC"),
        (tokyo, "Tokyo"),
        (london, "London"), 
        (new_york, "New York")
    ]
    
    for tz, city in timezones:
        # Convert meeting time to local time for each city
        if tz == utc:
            local_time = meeting_time_utc
        else:
            # Simple offset calculation for demonstration
            offset = tz.utcoffset(meeting_time_utc)
            local_time = meeting_time_utc.replace(tzinfo=None) + offset
            local_time = local_time.replace(tzinfo=tz)
        
        cur.execute(
            "INSERT INTO meetings (title, start_time, timezone, city) VALUES (%s, %s, %s, %s)",
            ("Global Team Meeting", local_time, tz.tzname(local_time), city)
        )
        
        print(f"{city}: {local_time} ({tz.tzname(local_time)})")
    
    # Query meetings with timezone information
    cur.execute("""
        SELECT title, start_time, timezone, city 
        FROM meetings 
        WHERE title = %s 
        ORDER BY start_time
    """, ("Global Team Meeting",))
    
    print("\nMeeting times by city:")
    for title, start_time, timezone, city in cur.fetchall():
        print(f"{city}: {start_time} {timezone}")

conn.commit()
conn.close()

PostgreSQL Timezone Integration

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

Usage Example:

import psycopg2
from psycopg2.tz import FixedOffsetTimezone, LOCAL
from datetime import datetime, timedelta

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

# Create table with timezone-aware columns
with conn.cursor() as cur:
    cur.execute("""
        CREATE TABLE IF NOT EXISTS user_activities (
            id SERIAL PRIMARY KEY,
            user_id INTEGER,
            activity_type VARCHAR(50),
            created_at TIMESTAMP WITH TIME ZONE,
            scheduled_at TIMESTAMP WITH TIME ZONE,
            local_time TIMESTAMP WITHOUT TIME ZONE
        )
    """)

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

with conn.cursor() as cur:
    # Insert with different timezone contexts
    activities = [
        (1, "login", datetime.now(utc), None, datetime.now()),
        (2, "logout", datetime.now(pst), None, datetime.now()),
        (1, "scheduled_task", datetime.now(LOCAL), 
         datetime.now(utc) + timedelta(hours=2), datetime.now())
    ]
    
    for user_id, activity, created_at, scheduled_at, local_time in activities:
        cur.execute("""
            INSERT INTO user_activities 
            (user_id, activity_type, created_at, scheduled_at, local_time)
            VALUES (%s, %s, %s, %s, %s)
        """, (user_id, activity, created_at, scheduled_at, local_time))
    
    # Query with timezone functions
    cur.execute("""
        SELECT 
            activity_type,
            created_at,
            created_at AT TIME ZONE 'UTC' as utc_time,
            created_at AT TIME ZONE 'America/Los_Angeles' as pst_time,
            extract(timezone from created_at) as tz_offset_seconds
        FROM user_activities
        ORDER BY created_at
    """)
    
    print("Activity timeline with timezone conversions:")
    for activity, created_at, utc_time, pst_time, tz_offset in cur.fetchall():
        print(f"{activity}:")
        print(f"  Original: {created_at}")
        print(f"  UTC: {utc_time}")
        print(f"  PST: {pst_time}")
        print(f"  TZ Offset: {tz_offset} seconds")
        print()

conn.commit()
conn.close()

Types

Timezone Class Interface

class FixedOffsetTimezone(datetime.tzinfo):
    """Fixed UTC offset timezone."""
    
    def __init__(self, offset: timedelta | None = None, name: str | None = None) -> None:
        """Initialize with offset and name."""
    
    def utcoffset(self, dt: datetime) -> timedelta:
        """Return UTC offset."""
    
    def tzname(self, dt: datetime) -> str:
        """Return timezone name."""
    
    def dst(self, dt: datetime) -> None:
        """Return DST offset (always None)."""

class LocalTimezone(datetime.tzinfo):
    """Platform local timezone."""
    
    def utcoffset(self, dt: datetime) -> timedelta:
        """Return local UTC offset."""
    
    def tzname(self, dt: datetime) -> str:
        """Return local timezone name."""
    
    def dst(self, dt: datetime) -> timedelta | None:
        """Return DST offset if applicable."""

Timezone Constants

LOCAL: LocalTimezone  # Pre-created local timezone instance
ZERO: timedelta      # Zero timedelta (timedelta(0))

Common Timezone Offsets

# Common timezone offsets (examples)
UTC_OFFSET = timedelta(0)           # UTC
EST_OFFSET = timedelta(hours=-5)    # Eastern Standard Time
PST_OFFSET = timedelta(hours=-8)    # Pacific Standard Time  
CET_OFFSET = timedelta(hours=1)     # Central European Time
JST_OFFSET = timedelta(hours=9)     # Japan Standard Time

Install with Tessl CLI