drizzle_core/expr/
datetime.rs

1//! Type-safe date/time functions.
2//!
3//! These functions work with `Temporal` types (Date, Time, Timestamp, TimestampTz)
4//! and provide compile-time enforcement of temporal operations.
5//!
6//! # Database Compatibility
7//!
8//! Some functions are database-specific:
9//! - SQLite: `date()`, `time()`, `datetime()`, `strftime()`, `julianday()`
10//! - PostgreSQL: `now()`, `date_trunc()`, `extract()`, `age()`
11//!
12//! Cross-database functions try to use compatible SQL where possible.
13
14use crate::sql::{SQL, Token};
15use crate::traits::SQLParam;
16use crate::types::{BigInt, Date, Double, Temporal, Text, Time, Timestamp};
17
18use super::{Expr, NullOr, Nullability, SQLExpr, Scalar};
19
20// =============================================================================
21// CURRENT DATE/TIME (Cross-database)
22// =============================================================================
23
24/// CURRENT_DATE - returns the current date.
25///
26/// Works on both SQLite and PostgreSQL.
27///
28/// # Example
29///
30/// ```ignore
31/// use drizzle_core::expr::current_date;
32///
33/// // SELECT CURRENT_DATE
34/// let today = current_date::<SQLiteValue>();
35/// ```
36pub fn current_date<'a, V>() -> SQLExpr<'a, V, Date, super::NonNull, Scalar>
37where
38    V: SQLParam + 'a,
39{
40    SQLExpr::new(SQL::raw("CURRENT_DATE"))
41}
42
43/// CURRENT_TIME - returns the current time.
44///
45/// Works on both SQLite and PostgreSQL.
46///
47/// # Example
48///
49/// ```ignore
50/// use drizzle_core::expr::current_time;
51///
52/// // SELECT CURRENT_TIME
53/// let now_time = current_time::<SQLiteValue>();
54/// ```
55pub fn current_time<'a, V>() -> SQLExpr<'a, V, Time, super::NonNull, Scalar>
56where
57    V: SQLParam + 'a,
58{
59    SQLExpr::new(SQL::raw("CURRENT_TIME"))
60}
61
62/// CURRENT_TIMESTAMP - returns the current timestamp.
63///
64/// Works on both SQLite and PostgreSQL.
65///
66/// # Example
67///
68/// ```ignore
69/// use drizzle_core::expr::current_timestamp;
70///
71/// // SELECT CURRENT_TIMESTAMP
72/// let now = current_timestamp::<SQLiteValue>();
73/// ```
74pub fn current_timestamp<'a, V>() -> SQLExpr<'a, V, Timestamp, super::NonNull, Scalar>
75where
76    V: SQLParam + 'a,
77{
78    SQLExpr::new(SQL::raw("CURRENT_TIMESTAMP"))
79}
80
81// =============================================================================
82// SQLite-specific DATE/TIME FUNCTIONS
83// =============================================================================
84
85/// DATE - extracts the date part from a temporal expression (SQLite).
86///
87/// Preserves the nullability of the input expression.
88///
89/// # Example
90///
91/// ```ignore
92/// use drizzle_core::expr::date;
93///
94/// // SELECT DATE(users.created_at)
95/// let created_date = date(users.created_at);
96/// ```
97pub fn date<'a, V, E>(expr: E) -> SQLExpr<'a, V, Date, E::Nullable, Scalar>
98where
99    V: SQLParam + 'a,
100    E: Expr<'a, V>,
101    E::SQLType: Temporal,
102{
103    SQLExpr::new(SQL::func("DATE", expr.into_sql()))
104}
105
106/// TIME - extracts the time part from a temporal expression (SQLite).
107///
108/// Preserves the nullability of the input expression.
109///
110/// # Example
111///
112/// ```ignore
113/// use drizzle_core::expr::time;
114///
115/// // SELECT TIME(users.created_at)
116/// let created_time = time(users.created_at);
117/// ```
118pub fn time<'a, V, E>(expr: E) -> SQLExpr<'a, V, Time, E::Nullable, Scalar>
119where
120    V: SQLParam + 'a,
121    E: Expr<'a, V>,
122    E::SQLType: Temporal,
123{
124    SQLExpr::new(SQL::func("TIME", expr.into_sql()))
125}
126
127/// DATETIME - creates a datetime from a temporal expression (SQLite).
128///
129/// Preserves the nullability of the input expression.
130///
131/// # Example
132///
133/// ```ignore
134/// use drizzle_core::expr::datetime;
135///
136/// // SELECT DATETIME(users.created_at)
137/// let dt = datetime(users.created_at);
138/// ```
139pub fn datetime<'a, V, E>(expr: E) -> SQLExpr<'a, V, Timestamp, E::Nullable, Scalar>
140where
141    V: SQLParam + 'a,
142    E: Expr<'a, V>,
143    E::SQLType: Temporal,
144{
145    SQLExpr::new(SQL::func("DATETIME", expr.into_sql()))
146}
147
148/// STRFTIME - formats a temporal expression as text (SQLite).
149///
150/// Returns Text type, preserves nullability of the time value.
151///
152/// # Format Specifiers (common)
153///
154/// - `%Y` - 4-digit year
155/// - `%m` - month (01-12)
156/// - `%d` - day of month (01-31)
157/// - `%H` - hour (00-23)
158/// - `%M` - minute (00-59)
159/// - `%S` - second (00-59)
160/// - `%s` - Unix timestamp
161/// - `%w` - day of week (0-6, Sunday=0)
162/// - `%j` - day of year (001-366)
163///
164/// # Example
165///
166/// ```ignore
167/// use drizzle_core::expr::strftime;
168///
169/// // SELECT STRFTIME('%Y-%m-%d', users.created_at)
170/// let formatted = strftime("%Y-%m-%d", users.created_at);
171/// ```
172pub fn strftime<'a, V, F, E>(format: F, expr: E) -> SQLExpr<'a, V, Text, E::Nullable, Scalar>
173where
174    V: SQLParam + 'a,
175    F: Expr<'a, V>,
176    E: Expr<'a, V>,
177    E::SQLType: Temporal,
178{
179    SQLExpr::new(SQL::func(
180        "STRFTIME",
181        format.into_sql().push(Token::COMMA).append(expr.into_sql()),
182    ))
183}
184
185/// JULIANDAY - converts a temporal expression to Julian day number (SQLite).
186///
187/// Returns Double type, preserves nullability.
188///
189/// # Example
190///
191/// ```ignore
192/// use drizzle_core::expr::julianday;
193///
194/// // SELECT JULIANDAY(users.created_at)
195/// let julian = julianday(users.created_at);
196/// ```
197pub fn julianday<'a, V, E>(expr: E) -> SQLExpr<'a, V, Double, E::Nullable, Scalar>
198where
199    V: SQLParam + 'a,
200    E: Expr<'a, V>,
201    E::SQLType: Temporal,
202{
203    SQLExpr::new(SQL::func("JULIANDAY", expr.into_sql()))
204}
205
206/// UNIXEPOCH - converts a temporal expression to Unix timestamp (SQLite 3.38+).
207///
208/// Returns BigInt type (seconds since 1970-01-01), preserves nullability.
209///
210/// # Example
211///
212/// ```ignore
213/// use drizzle_core::expr::unixepoch;
214///
215/// // SELECT UNIXEPOCH(users.created_at)
216/// let unix_ts = unixepoch(users.created_at);
217/// ```
218pub fn unixepoch<'a, V, E>(expr: E) -> SQLExpr<'a, V, BigInt, E::Nullable, Scalar>
219where
220    V: SQLParam + 'a,
221    E: Expr<'a, V>,
222    E::SQLType: Temporal,
223{
224    SQLExpr::new(SQL::func("UNIXEPOCH", expr.into_sql()))
225}
226
227// =============================================================================
228// PostgreSQL-specific DATE/TIME FUNCTIONS
229// =============================================================================
230
231/// NOW - returns the current timestamp with time zone (PostgreSQL).
232///
233/// # Example
234///
235/// ```ignore
236/// use drizzle_core::expr::now;
237///
238/// // SELECT NOW()
239/// let current = now::<PostgresValue>();
240/// ```
241pub fn now<'a, V>() -> SQLExpr<'a, V, Timestamp, super::NonNull, Scalar>
242where
243    V: SQLParam + 'a,
244{
245    SQLExpr::new(SQL::raw("NOW()"))
246}
247
248/// DATE_TRUNC - truncates a timestamp to specified precision (PostgreSQL).
249///
250/// Truncates the timestamp to the specified precision. Common values:
251/// 'microseconds', 'milliseconds', 'second', 'minute', 'hour',
252/// 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', 'millennium'
253///
254/// Preserves the nullability of the input expression.
255///
256/// # Example
257///
258/// ```ignore
259/// use drizzle_core::expr::date_trunc;
260///
261/// // SELECT DATE_TRUNC('month', users.created_at)
262/// let month_start = date_trunc("month", users.created_at);
263/// ```
264pub fn date_trunc<'a, V, P, E>(
265    precision: P,
266    expr: E,
267) -> SQLExpr<'a, V, Timestamp, E::Nullable, Scalar>
268where
269    V: SQLParam + 'a,
270    P: Expr<'a, V>,
271    E: Expr<'a, V>,
272    E::SQLType: Temporal,
273{
274    SQLExpr::new(SQL::func(
275        "DATE_TRUNC",
276        precision
277            .into_sql()
278            .push(Token::COMMA)
279            .append(expr.into_sql()),
280    ))
281}
282
283/// EXTRACT - extracts a component from a temporal expression (PostgreSQL/Standard SQL).
284///
285/// Returns Double type. Common fields:
286/// 'year', 'month', 'day', 'hour', 'minute', 'second',
287/// 'dow' (day of week), 'doy' (day of year), 'epoch' (Unix timestamp)
288///
289/// Preserves the nullability of the input expression.
290///
291/// # Example
292///
293/// ```ignore
294/// use drizzle_core::expr::extract;
295///
296/// // SELECT EXTRACT(YEAR FROM users.created_at)
297/// let year = extract("YEAR", users.created_at);
298/// ```
299pub fn extract<'a, 'f, V, E>(field: &'f str, expr: E) -> SQLExpr<'a, V, Double, E::Nullable, Scalar>
300where
301    'f: 'a,
302    V: SQLParam + 'a,
303    E: Expr<'a, V>,
304    E::SQLType: Temporal,
305{
306    // EXTRACT uses special syntax: EXTRACT(field FROM timestamp)
307    SQLExpr::new(
308        SQL::raw("EXTRACT(")
309            .append(SQL::raw(field))
310            .append(SQL::raw(" FROM "))
311            .append(expr.into_sql())
312            .push(Token::RPAREN),
313    )
314}
315
316/// AGE - calculates the interval between two timestamps (PostgreSQL).
317///
318/// Returns Text (interval representation). The result is nullable if either input is nullable.
319///
320/// # Example
321///
322/// ```ignore
323/// use drizzle_core::expr::age;
324///
325/// // SELECT AGE(NOW(), users.created_at)
326/// let user_age = age(now(), users.created_at);
327/// ```
328pub fn age<'a, V, E1, E2>(
329    timestamp1: E1,
330    timestamp2: E2,
331) -> SQLExpr<'a, V, Text, <E1::Nullable as NullOr<E2::Nullable>>::Output, Scalar>
332where
333    V: SQLParam + 'a,
334    E1: Expr<'a, V>,
335    E1::SQLType: Temporal,
336    E2: Expr<'a, V>,
337    E2::SQLType: Temporal,
338    E1::Nullable: NullOr<E2::Nullable>,
339    E2::Nullable: Nullability,
340{
341    SQLExpr::new(SQL::func(
342        "AGE",
343        timestamp1
344            .into_sql()
345            .push(Token::COMMA)
346            .append(timestamp2.into_sql()),
347    ))
348}
349
350/// TO_CHAR - formats a temporal expression as text (PostgreSQL).
351///
352/// Returns Text type, preserves nullability of the input expression.
353///
354/// # Common Format Patterns
355///
356/// - `YYYY` - 4-digit year
357/// - `MM` - month (01-12)
358/// - `DD` - day of month (01-31)
359/// - `HH24` - hour (00-23)
360/// - `MI` - minute (00-59)
361/// - `SS` - second (00-59)
362/// - `Day` - full day name
363/// - `Month` - full month name
364///
365/// # Example
366///
367/// ```ignore
368/// use drizzle_core::expr::to_char;
369///
370/// // SELECT TO_CHAR(users.created_at, 'YYYY-MM-DD')
371/// let formatted = to_char(users.created_at, "YYYY-MM-DD");
372/// ```
373pub fn to_char<'a, V, E, F>(expr: E, format: F) -> SQLExpr<'a, V, Text, E::Nullable, Scalar>
374where
375    V: SQLParam + 'a,
376    E: Expr<'a, V>,
377    E::SQLType: Temporal,
378    F: Expr<'a, V>,
379{
380    SQLExpr::new(SQL::func(
381        "TO_CHAR",
382        expr.into_sql().push(Token::COMMA).append(format.into_sql()),
383    ))
384}
385
386/// TO_TIMESTAMP - converts a Unix timestamp to a timestamp (PostgreSQL).
387///
388/// Returns Timestamp type. The input should be a numeric Unix timestamp.
389///
390/// # Example
391///
392/// ```ignore
393/// use drizzle_core::expr::to_timestamp;
394///
395/// // SELECT TO_TIMESTAMP(users.created_unix)
396/// let ts = to_timestamp(users.created_unix);
397/// ```
398pub fn to_timestamp<'a, V, E>(expr: E) -> SQLExpr<'a, V, Timestamp, E::Nullable, Scalar>
399where
400    V: SQLParam + 'a,
401    E: Expr<'a, V>,
402{
403    SQLExpr::new(SQL::func("TO_TIMESTAMP", expr.into_sql()))
404}
drizzle_core/expr/datetime.rs

drizzle_core/expr/
datetime.rs
