תאריך ושעה ב-SQLite: strftime, date(), datetime() ו-modifiers

ל-SQLite אין באמת טיפוס תאריך

זה מפיל כל מי שמגיע מ-Postgres או מ-MySQL. ל-SQLite יש חמש מחלקות אחסון, NULL, INTEGER, REAL, TEXT, BLOB, וזהו. אין DATE, אין DATETIME, אין TIMESTAMP. אפשר לכתוב created_at DATETIME ב-CREATE TABLE, ו-SQLite תקבל את זה, אבל היא שומרת את הערך כטקסט רגיל או כמספר.

מה ש-SQLite כן נותנת לכם הוא סט של פונקציות שמבינות שלושה פורמטים מקובלים:

• טקסט ב-ISO 8601: '2026-04-23', '2026-04-23 10:15:00', '2026-04-23T10:15:00.123Z'.
• חותמת זמן של Unix: שניות מאז 1970-01-01 UTC, שנשמרות כמספר שלם.
• מספר יום יוליאני: ימים וחלקי ימים מאז 4714 לפנה"ס, שנשמרים כ-real.

בחרו אחד והישארו איתו. טקסט ב-ISO 8601 הוא הקריא ביותר וממוין נכון כמחרוזת, ולכן הוא ברירת המחדל.

SELECT date('now'),
       datetime('now'),
       strftime('%s', 'now'),
       julianday('now');

ארבע דרכים לבקש "ממש עכשיו": תאריך כטקסט, תאריך ושעה כטקסט, שניות Unix, יום יוליאני. כולן מתארות את אותו רגע.

חמש פונקציות התאריך

SQLite נותנת לכם חמש פונקציות מובנות שמכסות כמעט הכול:

• date(time, ...): מחזירה YYYY-MM-DD.
• time(time, ...): מחזירה HH:MM:SS.
• datetime(time, ...): מחזירה YYYY-MM-DD HH:MM:SS.
• julianday(time, ...): מחזירה real (מצוין להפרשים).
• strftime(format, time, ...): מחזירה מחרוזת בפורמט מותאם.

כל אחת מקבלת ערך זמן כארגומנט ראשון, ואחריו כל מספר של מחרוזות modifier.

SELECT date('2026-04-23 10:15:00')          AS just_date,
       time('2026-04-23 10:15:00')          AS just_time,
       datetime(1745402100, 'unixepoch')    AS from_unix,
       julianday('2026-04-23')              AS julian;

שימו לב ל-'unixepoch': כך אומרים לפונקציות התאריך שהמספר השלם בקלט הוא חותמת זמן של Unix ולא יום יוליאני. בלי זה, SQLite מניחה שהמספר יוליאני.

strftime: פורמטים מותאמים לתאריכים

strftime היא סוס העבודה. אותם קודי % שאתם מכירים מ-C או מ-Python:

SELECT strftime('%Y-%m-%d',           'now') AS iso_date,
       strftime('%d/%m/%Y',           'now') AS european,
       strftime('%H:%M',              'now') AS hours_minutes,
       strftime('%Y-%m-%dT%H:%M:%fZ', 'now') AS iso_with_ms,
       strftime('%w',                 'now') AS day_of_week,
       strftime('%j',                 'now') AS day_of_year;

הקודים שתשתמשו בהם הכי הרבה:

• %Y: שנה בארבע ספרות.
• %m: חודש (01-12).
• %d: יום בחודש (01-31).
• %H, %M, %S: שעות, דקות, שניות.
• %w: יום בשבוע (0=ראשון).
• %j: יום בשנה (001-366).
• %s: חותמת זמן של Unix.
• %f: שניות עם חלקי שנייה (SS.SSS).

strftime היא גם הדרך לחלץ חלקים מתאריך: אין פונקציה נפרדת בשם EXTRACT או YEAR(). פשוט מעצבים את התאריך רק לחלק שרוצים, וממירים אם צריך מספר:

SELECT strftime('%Y', '2026-04-23') AS year_text,
       CAST(strftime('%Y', '2026-04-23') AS INTEGER) AS year_num,
       CAST(strftime('%m', '2026-04-23') AS INTEGER) AS month_num;

strftime תמיד מחזירה טקסט, אז עטפו אותה ב-CAST(... AS INTEGER) כשאתם רוצים לעשות חשבון או להשוות מספרית.

Modifiers: חשבון תאריכים בלי אופרטור

זו התכונה שהופכת את הטיפול בתאריכים ב-SQLite לנעים. אחרי ארגומנט הזמן, אפשר להעביר כל מספר של מחרוזות modifier, והן מוחלות לפי הסדר:

SELECT date('2026-04-23', '+7 days')              AS week_later,
       date('2026-04-23', '-1 month')             AS month_earlier,
       datetime('now', '+2 hours', '+30 minutes') AS in_two_thirty,
       date('now', 'start of month')              AS first_of_month,
       date('now', 'start of month', '+1 month', '-1 day') AS last_of_month,
       date('now', 'weekday 0')                   AS next_sunday;

ה-modifiers שתשתמשו בהם כל הזמן:

• '+N days', '-N days', ואותו דבר עבור hours, minutes, seconds, months, years.
• 'start of day', 'start of month', 'start of year': חיתוך לגבול הזה.
• 'weekday N': התקדמות ליום השבוע הבא שצוין (0=ראשון).
• 'localtime' ו-'utc': המרה בין אזורי זמן.

הטריק של "היום האחרון בחודש", תחילת החודש, ועוד חודש, פחות יום, שווה שינון. ל-SQLite אין פונקציית LAST_DAY, אבל שרשור modifiers נותן את אותו דבר.

UTC מול שעון מקומי

'now' מחזיר תמיד UTC. אם אתם רוצים שעון מקומי, צריך לבקש:

SELECT datetime('now')              AS utc_now,
       datetime('now', 'localtime') AS local_now,
       datetime('2026-04-23 10:00:00', 'utc') AS treated_as_local_then_utc;

ה-modifier 'localtime' ממיר ערך UTC לאזור הזמן המקומי של המערכת. ה-modifier 'utc' עושה את ההפך: הוא מתייחס לקלט כשעון מקומי וממיר ל-UTC.

ההרגל הבטוח: שמרו הכול ב-UTC, והמירו לשעון מקומי רק בזמן התצוגה. ערבוב אזורי זמן באחסון מוביל לבאגים שמופיעים פעמיים בשנה, כששעון הקיץ משתנה.

השוואת תאריכים וסינון טווחים

אם שומרים תאריכים כטקסט ב-ISO 8601, השוואה ו-BETWEEN פשוט עובדים: ISO 8601 ממוין לפי סדר מילוני באותו אופן שהוא ממוין כרונולוגית. זו כל הסיבה שהוא פורמט ברירת המחדל.

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL  -- ISO 8601
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00');

SELECT customer, placed_at
FROM orders
WHERE placed_at >= '2026-04-01'
  AND placed_at <  '2026-05-01'
ORDER BY placed_at;

הטווח החצי פתוח (>= start, < end) הוא הרגל שימושי: הוא מונע לגמרי את השאלה "האם חצות של ה-30 נכללה או לא?".

ל"שבעת הימים האחרונים", תנו ל-SQLite לחשב את הגבול:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer, placed_at
FROM orders
WHERE placed_at >= datetime('now', '-7 days')
ORDER BY placed_at DESC;

הפרשי תאריכים

ל-SQLite אין DATEDIFF. שני דפוסים מכסים הכול:

SELECT julianday('2026-04-23') - julianday('2026-04-01')           AS days_between,
       (julianday('2026-04-23 18:00') - julianday('2026-04-23 09:00')) * 24 AS hours_between,
       strftime('%s', '2026-04-23 18:00') - strftime('%s', '2026-04-23 09:00') AS seconds_between;

הפרשים של julianday() הם בימים (עם דיוק של חלקי ימים), כך שהכפלה ב-24 נותנת שעות, וב-1440 נותנת דקות. הפרשים של strftime('%s', ...) הם בשניות: נוח כשרוצים מספר שלם.

CAST(... AS INTEGER) חותך חלקי ימים אם אתם רוצים ספירה של ימים שלמים:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer,
       CAST(julianday('now') - julianday(placed_at) AS INTEGER) AS days_ago
FROM orders
ORDER BY days_ago;

שמירת תאריכים: בחרו פורמט אחד והתחייבו אליו

שלוש בחירות סבירות, לפי הסדר של כמה פעמים כדאי לבחור בהן:

1. טקסט ב-ISO 8601 (TEXT). קריא בקבצי dump, ממוין נכון, מסתדר טוב עם כל פונקציות התאריך. בחירת ברירת המחדל.
2. שניות Unix (INTEGER). קומפקטי, השוואות מהירות, בלי עמימות של אזורי זמן. טוב כשיש לכם מיליוני שורות. תצטרכו datetime(col, 'unixepoch') כדי לקרוא אותו בחזרה.
3. יום יוליאני (REAL). כמעט אף פעם לא שווה את זה, אלא אם אתם עושים חשבון תאריכים כבד ורוצים דיוק של פחות משנייה בעמודה אחת.

מה ש_לא_ כדאי לעשות זה לערבב פורמטים באותה עמודה. פונקציות התאריך יקבלו בשקט כל אחד מהם, אבל אינדקסים, מיונים והשוואות יפיקו שטויות.

-- ערכי ברירת מחדל שמתמלאים אוטומטית בזמן ההכנסה:
CREATE TABLE events (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    happened_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO events (name) VALUES ('signup'), ('login');

SELECT name, happened_at FROM events;

DEFAULT (datetime('now')) היא המקבילה ב-SQLite ל-DEFAULT CURRENT_TIMESTAMP: היא מחתימה כל שורה חדשה בזמן ה-UTC הנוכחי בלי שום קוד באפליקציה.

קיבוץ לפי תקופת זמן

strftime זוהרת כשרוצים לחלק שורות לדליים לפי חודש, שבוע או שעה:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00'),
    ('Ezra',  '2026-05-20 17:00:00');

SELECT strftime('%Y-%m', placed_at) AS month,
       COUNT(*)                     AS order_count
FROM orders
GROUP BY month
ORDER BY month;

אותו רעיון עובד ל"הזמנות לפי שעה ביום", "הרשמות לפי יום בשבוע" או "אירועים לפי דקה": בחרו מחרוזת פורמט שתופסת רק את הרזולוציה שאתם רוצים, קבצו לפיה, וצברו.

הבא בתור: פונקציות צבירה

ואם כבר מדברים על קיבוץ, ה-COUNT(*) הזה הוא הפשוטה מבין פונקציות הצבירה של SQLite. בעמוד הבא נסתכל על הסט המלא: SUM, AVG, MIN, MAX, ואיך הן מכווצות שורות רבות לערך סיכום אחד.