Lavoro con valori data-time

Il driver mssql-python gestisce i tipi di data e ora di SQL Server e li mappa agli oggetti moduli di datetime Python. SQL Server fornisce molteplici tipi di data e ora con precisione e capacità variabili:

Inserire valori data-ora

Usa oggetti datetime di Python

Il seguente esempio inserisce diversi tipi di data e ora in SQL Server utilizzando il modulo di datetime Python:

from datetime import datetime, date, time
import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Create a temp table with date, time, and datetime columns
cursor.execute("""
    CREATE TABLE #Events (
        EventDate DATE,
        StartTime TIME,
        LogTime DATETIME2
    )
""")

# Insert date
cursor.execute(
    "INSERT INTO #Events (EventDate) VALUES (%(date)s)",
    {"date": date(2024, 12, 25)}
)

# Insert time
cursor.execute(
    "INSERT INTO #Events (StartTime) VALUES (%(time)s)",
    {"time": time(14, 30, 0)}
)

# Insert datetime
cursor.execute(
    "INSERT INTO #Events (LogTime) VALUES (%(ts)s)",
    {"ts": datetime(2024, 3, 15, 14, 30, 45)}
)

conn.commit()

Inserisci il timestamp attuale

Puoi inserire la data e l'ora correnti usando datetime.now() o la funzione GETDATE() di SQL Server:

from datetime import datetime

# Create temp table for the demo
cursor.execute("CREATE TABLE #Logs (Message NVARCHAR(200), CreatedAt DATETIME2)")

# Python current time
now = datetime.now()
cursor.execute("INSERT INTO #Logs (Message, CreatedAt) VALUES (%(msg)s, %(ts)s)",
               {"msg": "Event occurred", "ts": now})

# Or use SQL Server's GETDATE()
cursor.execute("INSERT INTO #Logs (Message, CreatedAt) VALUES (%(msg)s, GETDATE())",
               {"msg": "Event occurred"})
conn.commit()

Datetime2 ad alta precisione

Per timestamp di maggiore precisione, si utilizza il datetime2 tipo che supporta una precisione di 100 nanosecondi:

from datetime import datetime

# Create temp table with a datetime2(7) column
cursor.execute("CREATE TABLE #PreciseLogs (EventTime DATETIME2(7))")

# Microsecond precision (Python supports up to microseconds)
precise_time = datetime(2024, 3, 15, 14, 30, 45, 123456)

cursor.execute(
    "INSERT INTO #PreciseLogs (EventTime) VALUES (%(ts)s)",  # datetime2(7) column
    {"ts": precise_time}
)
conn.commit()

Recupero dei valori data-ora

Riportare date e orari

Quando si recuperano i valori datatime da SQL Server, vengono automaticamente convertiti in oggetti Pythondatetime:

from datetime import date, time, datetime

# Create and populate a temp table with date, time, and datetime columns
cursor.execute("""
    CREATE TABLE #Events (
        ID INT,
        EventDate DATE,
        StartTime TIME,
        CreatedAt DATETIME2
    )
""")
cursor.execute(
    "INSERT INTO #Events (ID, EventDate, StartTime, CreatedAt) VALUES (1, %(d)s, %(t)s, %(dt)s)",
    {"d": date(2024, 12, 25), "t": time(14, 30, 0), "dt": datetime(2024, 3, 15, 14, 30, 45)}
)
conn.commit()

cursor.execute("SELECT EventDate, StartTime, CreatedAt FROM #Events WHERE ID = 1")
row = cursor.fetchone()

print(type(row.EventDate))   # <class 'datetime.date'>
print(type(row.StartTime))   # <class 'datetime.time'>
print(type(row.CreatedAt))   # <class 'datetime.datetime'>

print(row.EventDate)    # 2024-12-25
print(row.StartTime)    # 14:30:00
print(row.CreatedAt)    # 2024-03-15 14:30:45

Accesso ai componenti datetime

Gli oggetti data-tempo Python forniscono attributi per accedere ai singoli componenti di data e ora:

cursor.execute("SELECT OrderDate FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659")
row = cursor.fetchone()
dt = row.OrderDate

# Date components
print(dt.year)
print(dt.month)
print(dt.day)

# Time components
print(dt.hour)
print(dt.minute)
print(dt.second)
print(dt.microsecond)

Fusi orari

Datetime con offset e datetime senza offset

Gli oggetti datetime di Python sono o offset-aware (have tzinfo) o offset-naïve (nessuna informazione sul fuso orario). SQL Server ha entrambi i tipi di colonne:

L'invio di una data e ora con fuso orario in una colonna datetime2 funziona, ma le informazioni sul fuso orario vengono eliminate senza alcuna segnalazione. Inviare una data-ora ingenua a una datetimeoffset colonna assegna UTC (+00:00) come offset. Sii esplicito su quale tipo invii:

Il seguente esempio mostra come creare e usare orari di data offset ingenui e consapevoli di offset:

from datetime import datetime, timezone, timedelta

# Create temp tables: one datetime2 column and one datetimeoffset column
cursor.execute("CREATE TABLE #Logs (LogTime DATETIME2)")
cursor.execute("CREATE TABLE #GlobalEvents (EventTime DATETIMEOFFSET)")

# Offset-naive - use for datetime2 columns
naive_dt = datetime(2024, 3, 15, 14, 30, 45)
cursor.execute(
    "INSERT INTO #Logs (LogTime) VALUES (%(log_time)s)",  # datetime2 column
    {"log_time": naive_dt}
)

# Offset-aware - use for datetimeoffset columns
eastern = timezone(timedelta(hours=-5))
aware_dt = datetime(2024, 3, 15, 14, 30, 45, tzinfo=eastern)
cursor.execute(
    "INSERT INTO #GlobalEvents (EventTime) VALUES (%(event_time)s)",  # datetimeoffset column
    {"event_time": aware_dt}
)
conn.commit()

Per convertire tra i due:

from datetime import datetime, timezone

# Make naive datetime timezone-aware
naive = datetime(2024, 3, 15, 14, 30)
aware = naive.replace(tzinfo=timezone.utc)

# Strip timezone from aware datetime
aware = datetime.now(timezone.utc)
naive = aware.replace(tzinfo=None)

tipo datetimeoffset

SQL Server datetimeoffset memorizza informazioni sul fuso orario. L'esempio seguente mostra come inserire e recuperare valori datetime con fuso orario:

from datetime import datetime, timezone, timedelta

# Create temp table with a datetimeoffset column
cursor.execute("CREATE TABLE #GlobalEvents (ID INT, EventTime DATETIMEOFFSET)")

# Create timezone-aware datetime
eastern = timezone(timedelta(hours=-5))
dt_eastern = datetime(2024, 3, 15, 14, 30, tzinfo=eastern)

cursor.execute(
    "INSERT INTO #GlobalEvents (ID, EventTime) VALUES (1, %(ts)s)",  # datetimeoffset column
    {"ts": dt_eastern}
)
conn.commit()

# Retrieve timezone-aware datetime
cursor.execute("SELECT EventTime FROM #GlobalEvents WHERE ID = 1")
row = cursor.fetchone()
print(row.EventTime)          # 2024-03-15 14:30:00-05:00
print(row.EventTime.tzinfo)   # UTC-05:00

Convertizione tra fusi orari

Usa i metodi di fuso orario di Python per convertire i valori di datetimeoffset tra diverse rappresentazioni di fusi orari:

from datetime import datetime, timezone, timedelta

# Create and populate a temp table with a datetimeoffset column
cursor.execute("CREATE TABLE #GlobalEvents (EventTime DATETIMEOFFSET)")
eastern = timezone(timedelta(hours=-5))
cursor.execute(
    "INSERT INTO #GlobalEvents (EventTime) VALUES (%(ts)s)",
    {"ts": datetime(2024, 3, 15, 14, 30, tzinfo=eastern)}
)
conn.commit()

# Retrieve as-stored
cursor.execute("SELECT EventTime FROM #GlobalEvents")
row = cursor.fetchone()
stored_time = row.EventTime  # Has timezone info

# Convert to UTC
utc_time = stored_time.astimezone(timezone.utc)
print(utc_time)

# Convert to local timezone
local_time = stored_time.astimezone()  # System timezone
print(local_time)

AT TIME ZONE in SQL

La clausola di AT TIME ZONE SQL Server converte i valori di datetimeoffset tra fusi orari all'interno di una query:

from datetime import datetime, timezone, timedelta

# Create and populate a temp table with a datetimeoffset column
cursor.execute("CREATE TABLE #GlobalEvents (EventTime DATETIMEOFFSET)")
eastern = timezone(timedelta(hours=-5))
cursor.execute(
    "INSERT INTO #GlobalEvents (EventTime) VALUES (%(ts)s)",
    {"ts": datetime(2024, 3, 15, 14, 30, tzinfo=eastern)}
)
conn.commit()

# Convert timezone in SQL Server (2016+)
cursor.execute("""
    SELECT 
        EventTime,
        EventTime AT TIME ZONE 'Pacific Standard Time' AS PacificTime,
        EventTime AT TIME ZONE 'UTC' AS UTCTime
    FROM #GlobalEvents
""")

for row in cursor:
    print(f"Original: {row.EventTime}")
    print(f"Pacific: {row.PacificTime}")
    print(f"UTC: {row.UTCTime}")

Aritmetica delle date

Calcolare le differenze tra date

Calcola il numero di giorni tra due date sottraendo oggetti data-orario:

from datetime import timedelta

cursor.execute("SELECT OrderDate, ShipDate FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659")
row = cursor.fetchone()

# Days between dates
if row.ShipDate and row.OrderDate:
    difference = row.ShipDate - row.OrderDate
    print(f"Shipped in {difference.days} days")

Aggiungi intervalli

Da usare timedelta per aggiungere o sottrarre giorni, mesi o altri intervalli da una data:

from datetime import datetime, timedelta

# Create and populate a temp table
cursor.execute("""
    CREATE TABLE #Subscriptions (
        ID INT,
        CreatedAt DATETIME2,
        ExpiresAt DATETIME2
    )
""")
cursor.execute(
    "INSERT INTO #Subscriptions (ID, CreatedAt) VALUES (1, %(created)s)",
    {"created": datetime(2024, 1, 1, 12, 0, 0)}
)
conn.commit()

cursor.execute("SELECT CreatedAt FROM #Subscriptions WHERE ID = 1")
row = cursor.fetchone()

# Calculate expiration (30 days from creation)
expiration = row.CreatedAt + timedelta(days=30)
print(f"Expires: {expiration}")

# Update with calculated date
cursor.execute(
    "UPDATE #Subscriptions SET ExpiresAt = %(exp)s WHERE ID = %(id)s",
    {"exp": expiration, "id": 1}
)
conn.commit()

DATEADD in SQL

La funzione di DATEADD SQL Server esegue direttamente l'aritmetica delle date nelle query:

# Use SQL Server date functions
cursor.execute("""
    SELECT 
        OrderDate,
        DATEADD(day, 30, OrderDate) AS DueDate,
        DATEADD(month, 1, OrderDate) AS NextMonth
    FROM Sales.SalesOrderHeader
""")

Formattare i valori

Formato per la visualizzazione

Valori di data-tempo per la visualizzazione utilizzando strftime() con specificatori di formato:

from datetime import datetime

cursor.execute("SELECT TOP(10) OrderDate FROM Sales.SalesOrderHeader")

for row in cursor:
    # strftime formatting
    print(row.OrderDate.strftime("%Y-%m-%d"))           # 2024-03-15
    print(row.OrderDate.strftime("%B %d, %Y"))          # March 15, 2024
    print(row.OrderDate.strftime("%m/%d/%Y %I:%M %p"))  # 03/15/2024 02:30 PM

Analizza a partire dalle stringhe

Converti le rappresentazioni delle stringhe in oggetti datetime Python usando strptime():

from datetime import datetime

# Create temp table for the demo
cursor.execute("CREATE TABLE #Events (EventDate DATE)")

# Parse incoming date strings
date_string = "2024-03-15"
parsed_date = datetime.strptime(date_string, "%Y-%m-%d").date()

cursor.execute(
    "INSERT INTO #Events (EventDate) VALUES (%(date)s)",
    {"date": parsed_date}
)
conn.commit()

Formato ISO 8601

Il formato ISO 8601 è utile per API e serializzazione JSON. Usa isoformat() e fromisoformat() per la conversione:

from datetime import datetime

# Create and populate a temp table
cursor.execute("CREATE TABLE #Logs (ID INT, CreatedAt DATETIME2)")
cursor.execute(
    "INSERT INTO #Logs (ID, CreatedAt) VALUES (1, %(ts)s)",
    {"ts": datetime(2024, 3, 15, 14, 30, 45)}
)
conn.commit()

cursor.execute("SELECT CreatedAt FROM #Logs WHERE ID = 1")
row = cursor.fetchone()

# ISO format for APIs/JSON
iso_string = row.CreatedAt.isoformat()
print(iso_string)  # 2024-03-15T14:30:45

# Parse ISO format
dt = datetime.fromisoformat("2024-03-15T14:30:45")

Modelli comuni

Consulta per intervallo di date

Consulta i record all'interno di un intervallo di date specifico parametrizzando le date di inizio e fine:

from datetime import date, datetime

# Query by date range
start_date = date(2024, 1, 1)
end_date = date(2024, 12, 31)

cursor.execute("""
    SELECT SalesOrderID, OrderDate, TotalDue
    FROM Sales.SalesOrderHeader
    WHERE OrderDate >= %(start)s AND OrderDate < %(end)s
""", {"start": start_date, "end": end_date})

Consulta i registri di oggi

Recuperare i record creati oggi convertendo la colonna datetime in data e confrontandola con la data odierna:

from datetime import date

# Create and populate a temp table with a row logged today
cursor.execute("CREATE TABLE #Logs (LogTime DATETIME2)")
cursor.execute("INSERT INTO #Logs (LogTime) VALUES (GETDATE())")
conn.commit()

cursor.execute("""
    SELECT * FROM #Logs 
    WHERE CAST(LogTime AS DATE) = %(today)s
""", {"today": date.today()})

# Or using SQL Server function
cursor.execute("""
    SELECT * FROM #Logs 
    WHERE CAST(LogTime AS DATE) = CAST(GETDATE() AS DATE)
""")

Gestire date NULL

Controlla i None valori prima di elaborare le colonne datetime, poiché le date NULL appaiono come None in Python:

cursor.execute("SELECT TOP 5 FirstName, ModifiedDate FROM Person.Person")

for row in cursor:
    if row.ModifiedDate is None:
        print(f"{row.FirstName}: Date not on file")
    else:
        age = (date.today() - row.ModifiedDate.date()).days // 365
        print(f"{row.FirstName}: modified {age} years ago")

Memorizza i timestamp di controllo

Crea colonne di audit per tracciare quando vengono creati e modificati i record:

from datetime import datetime

# Create temp table for audit timestamp demo
cursor.execute("""
    CREATE TABLE #AuditOrders (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        CustomerID INT,
        Total DECIMAL(10,2),
        CreatedAt DATETIME2,
        ModifiedAt DATETIME2
    )
""")

def create_order(cursor, customer_id: int, total: float) -> int:
    now = datetime.now()
    cursor.execute("""
        INSERT INTO #AuditOrders (CustomerID, Total, CreatedAt, ModifiedAt)
        VALUES (%(cust)s, %(total)s, %(created)s, %(modified)s);
        SELECT SCOPE_IDENTITY();
    """, {
        "cust": customer_id,
        "total": total,
        "created": now,
        "modified": now
    })
    return cursor.fetchval()

def update_order(cursor, order_id: int, total: float):
    cursor.execute("""
        UPDATE #AuditOrders
        SET Total = %(total)s, ModifiedAt = %(modified)s
        WHERE ID = %(id)s
    """, {
        "total": total,
        "modified": datetime.now(),
        "id": order_id
    })

Scenari specifici

Date precedenti al 1753

Uso datetime2 per date storiche (il datetime tipo supporta solo date dal 1753). Il seguente esempio inserisce una data storica:

from datetime import datetime

# Create temp table with a datetime2 column
cursor.execute("CREATE TABLE #HistoricalEvents (EventDate DATETIME2, Description NVARCHAR(200))")

# Historical date (works with datetime2, fails with datetime)
historical = datetime(1500, 1, 1)

cursor.execute(
    "INSERT INTO #HistoricalEvents (EventDate, Description) VALUES (%(date)s, %(desc)s)",
    {"date": historical, "desc": "Archived historical record"}
)
conn.commit()

Valori solo temporali

Memorizza i valori dell'ora del giorno usando l'timeoggetto di Python:

from datetime import time

# Create temp table with time columns
cursor.execute("""
    CREATE TABLE #BusinessHours (
        DayOfWeek NVARCHAR(10),
        OpenTime TIME,
        CloseTime TIME
    )
""")

# Store time of day without date
opening_time = time(9, 0, 0)   # 9:00 AM
closing_time = time(17, 30, 0) # 5:30 PM

cursor.execute("""
    INSERT INTO #BusinessHours (DayOfWeek, OpenTime, CloseTime)
    VALUES (%(day)s, %(open)s, %(close)s)
""", {"day": "Monday", "open": opening_time, "close": closing_time})
conn.commit()

Calcolare i giorni lavorativi

Una funzione helper può calcolare intervalli di appuntamenti saltando i fine settimana:

from datetime import date, timedelta

def add_business_days(start: date, days: int) -> date:
    """Add business days (skip weekends)."""
    current = start
    remaining = days
    while remaining > 0:
        current += timedelta(days=1)
        if current.weekday() < 5:  # Monday = 0, Friday = 4
            remaining -= 1
    return current

order_date = date(2024, 3, 15)  # Friday
due_date = add_business_days(order_date, 5)  # Skip weekend
print(f"Due date: {due_date}")  # 2024-03-22 (next Friday)

Contenuti correlati

• Mapping dei tipi di dati
• Gestire i valori NULL
• Recupero dei dati