Custom Types

class PGInterval:
    """
    PostgreSQL interval type with comprehensive time component support.
    
    Properties:
    - millennia: int - Number of millennia
    - centuries: int - Number of centuries  
    - decades: int - Number of decades
    - years: int - Number of years
    - months: int - Number of months
    - weeks: int - Number of weeks
    - days: int - Number of days
    - hours: int - Number of hours
    - minutes: int - Number of minutes
    - seconds: int - Number of seconds
    - microseconds: int - Number of microseconds
    """
    
    def __init__(
        self,
        millennia: int = 0,
        centuries: int = 0,
        decades: int = 0,
        years: int = 0,
        months: int = 0,
        weeks: int = 0,
        days: int = 0,
        hours: int = 0,
        minutes: int = 0,
        seconds: int = 0,
        microseconds: int = 0
    ):
        """
        Create PGInterval with specified time components.
        
        Parameters:
        - millennia: Number of millennia
        - centuries: Number of centuries
        - decades: Number of decades
        - years: Number of years
        - months: Number of months
        - weeks: Number of weeks
        - days: Number of days
        - hours: Number of hours
        - minutes: Number of minutes
        - seconds: Number of seconds
        - microseconds: Number of microseconds
        """
    
    @classmethod
    def from_str(cls, interval_str: str) -> PGInterval:
        """
        Parse interval from string representation.
        
        Parameters:
        - interval_str: String representation of interval
        
        Returns:
        PGInterval object parsed from string
        
        Raises:
        ValueError: If string format is invalid
        """
    
    @classmethod
    def from_str_iso_8601(cls, interval_str: str) -> PGInterval:
        """
        Parse interval from ISO 8601 duration format.
        
        Parameters:
        - interval_str: ISO 8601 duration string (e.g., "P1Y2M3DT4H5M6S")
        
        Returns:
        PGInterval object parsed from ISO 8601 format
        
        Raises:
        ValueError: If ISO 8601 format is invalid
        """
    
    @classmethod
    def from_str_postgres(cls, interval_str: str) -> PGInterval:
        """
        Parse interval from PostgreSQL native format.
        
        Parameters:
        - interval_str: PostgreSQL interval string (e.g., "1 year 2 months")
        
        Returns:
        PGInterval object parsed from PostgreSQL format
        
        Raises:
        ValueError: If PostgreSQL format is invalid
        """
    
    @classmethod
    def from_str_sql_standard(cls, interval_str: str) -> PGInterval:
        """
        Parse interval from SQL standard format.
        
        Parameters:
        - interval_str: SQL standard interval string
        
        Returns:
        PGInterval object parsed from SQL standard format
        
        Raises:
        ValueError: If SQL standard format is invalid
        """
    
    def normalize(self) -> PGInterval:
        """
        Normalize interval to standard units.
        
        Converts larger units to smaller units where possible
        (e.g., 24 hours to 1 day).
        
        Returns:
        New normalized PGInterval object
        """
    
    def to_timedelta(self) -> datetime.timedelta:
        """
        Convert to Python timedelta object.
        
        Note: Only day, hour, minute, second, and microsecond components
        are included as timedelta doesn't support months/years.
        
        Returns:
        Python timedelta object with convertible components
        
        Raises:
        ValueError: If interval contains non-convertible components
        """
    
    def __str__(self) -> str:
        """
        String representation of interval.
        
        Returns:
        Human-readable interval string
        """
    
    def __repr__(self) -> str:
        """
        Developer representation of interval.
        
        Returns:
        Detailed interval representation
        """
    
    def __eq__(self, other: object) -> bool:
        """
        Test equality with another PGInterval.
        
        Parameters:
        - other: Object to compare with
        
        Returns:
        True if intervals are equal
        """

class Range:
    """
    PostgreSQL range type supporting various boundary conditions.
    
    Properties:
    - lower: object - Lower bound value (None for unbounded)
    - upper: object - Upper bound value (None for unbounded)  
    - bounds: str - Boundary inclusion string ("[)", "[]", "(]", "()")
    - is_empty: bool - Whether range is empty
    """
    
    def __init__(
        self,
        lower: object = None,
        upper: object = None,
        bounds: str = "[)",
        is_empty: bool = False
    ):
        """
        Create range with specified bounds and inclusivity.
        
        Parameters:
        - lower: Lower bound value (None for unbounded)
        - upper: Upper bound value (None for unbounded)
        - bounds: Boundary specification string:
          - "[)" : Lower inclusive, upper exclusive (default)
          - "[]" : Both bounds inclusive
          - "(]" : Lower exclusive, upper inclusive  
          - "()" : Both bounds exclusive
        - is_empty: Whether to create an empty range
        
        Raises:
        ValueError: If bounds format is invalid
        """
    
    @property
    def lower_inc(self) -> bool:
        """
        Whether lower bound is inclusive.
        
        Returns:
        True if lower bound is inclusive
        """
    
    @property
    def upper_inc(self) -> bool:
        """
        Whether upper bound is inclusive.
        
        Returns:
        True if upper bound is inclusive
        """
    
    @property
    def lower_inf(self) -> bool:
        """
        Whether lower bound is infinite (unbounded).
        
        Returns:
        True if lower bound is infinite
        """
    
    @property  
    def upper_inf(self) -> bool:
        """
        Whether upper bound is infinite (unbounded).
        
        Returns:
        True if upper bound is infinite
        """
    
    def __contains__(self, value: object) -> bool:
        """
        Test if value is contained in range.
        
        Parameters:
        - value: Value to test for containment
        
        Returns:
        True if value is within range bounds
        """
    
    def __str__(self) -> str:
        """
        String representation of range.
        
        Returns:
        Range string in PostgreSQL format
        """
    
    def __repr__(self) -> str:
        """
        Developer representation of range.
        
        Returns:
        Detailed range representation
        """
    
    def __eq__(self, other: object) -> bool:
        """
        Test equality with another Range.
        
        Parameters:
        - other: Object to compare with
        
        Returns:
        True if ranges are equal
        """

import pg8000
from pg8000 import PGInterval
import datetime

# Create intervals using different methods
interval1 = PGInterval(years=1, months=6, days=15, hours=3, minutes=30)
interval2 = PGInterval.from_str_iso_8601("P1Y6M15DT3H30M")
interval3 = PGInterval.from_str_postgres("1 year 6 months 15 days 03:30:00")

print(f"Interval 1: {interval1}")
print(f"Interval 2: {interval2}")
print(f"Interval 3: {interval3}")

# Normalize interval
normalized = interval1.normalize()
print(f"Normalized: {normalized}")

# Convert to timedelta (only day/time components)
try:
    td = PGInterval(days=5, hours=3, minutes=30).to_timedelta()
    print(f"As timedelta: {td}")
except ValueError as e:
    print(f"Cannot convert: {e}")

# Use with database
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
cursor = conn.cursor()

cursor.execute("""
    INSERT INTO events (name, duration) 
    VALUES (%s, %s)
""", ("Long Process", interval1))

cursor.execute("SELECT name, duration FROM events WHERE id = %s", (1,))
row = cursor.fetchone()
print(f"Event: {row[0]}, Duration: {row[1]}")

cursor.close()
conn.close()

import pg8000
from pg8000 import Range
import datetime

# Create different types of ranges
int_range = Range(lower=10, upper=20, bounds="[)")  # [10, 20)
date_range = Range(
    lower=datetime.date(2023, 1, 1),
    upper=datetime.date(2023, 12, 31),
    bounds="[]"  # [2023-01-01, 2023-12-31]
)
unbounded_range = Range(lower=100, upper=None, bounds="[)")  # [100, ∞)
empty_range = Range(is_empty=True)

print(f"Integer range: {int_range}")
print(f"Date range: {date_range}")
print(f"Unbounded range: {unbounded_range}")
print(f"Empty range: {empty_range}")

# Test containment
print(f"15 in int_range: {15 in int_range}")          # True
print(f"20 in int_range: {20 in int_range}")          # False (exclusive upper)
print(f"5 in int_range: {5 in int_range}")            # False

# Check boundary properties
print(f"Lower inclusive: {int_range.lower_inc}")      # True
print(f"Upper inclusive: {int_range.upper_inc}")      # False
print(f"Upper infinite: {unbounded_range.upper_inf}") # True

# Use with database
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS price_ranges (
        id SERIAL PRIMARY KEY,
        category TEXT,
        price_range int4range
    )
""")

cursor.execute("""
    INSERT INTO price_ranges (category, price_range) 
    VALUES (%s, %s)
""", ("Budget", int_range))

cursor.execute("""
    SELECT category, price_range 
    FROM price_ranges 
    WHERE price_range @> %s
""", (15,))  # Find ranges containing value 15

row = cursor.fetchone()
if row:
    print(f"Category: {row[0]}, Range: {row[1]}")

cursor.close()
conn.close()

import pg8000
from pg8000 import Range
import datetime

conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
cursor = conn.cursor()

# Create table with different range types
cursor.execute("""
    CREATE TABLE IF NOT EXISTS bookings (
        id SERIAL PRIMARY KEY,
        room_number INT,
        date_range daterange,
        time_range tsrange,
        price_range numrange
    )
""")

# Insert booking with multiple range types
date_range = Range(
    lower=datetime.date(2023, 12, 20),
    upper=datetime.date(2023, 12, 25),
    bounds="[]"
)

time_range = Range(
    lower=datetime.datetime(2023, 12, 20, 14, 0),
    upper=datetime.datetime(2023, 12, 25, 12, 0),
    bounds="[)"
)

price_range = Range(lower=100.00, upper=500.00, bounds="[]")

cursor.execute("""
    INSERT INTO bookings (room_number, date_range, time_range, price_range)
    VALUES (%s, %s, %s, %s)
""", (101, date_range, time_range, price_range))

# Query overlapping ranges
cursor.execute("""
    SELECT room_number, date_range 
    FROM bookings 
    WHERE date_range && %s
""", (Range(
    lower=datetime.date(2023, 12, 22), 
    upper=datetime.date(2023, 12, 28),
    bounds="[]"
),))

for row in cursor.fetchall():
    print(f"Room {row[0]} booked during: {row[1]}")

cursor.close()
conn.close()