drizzle_core/expr/
datetime.rs

1//! Date and time functions.
2//!
3//! Arguments that hold a date or time must have a temporal SQL type (on
4//! SQLite, text columns count as temporal, since SQLite stores dates as
5//! text). Many functions exist on only one database and do not compile for
6//! the others:
7//!
8//! - all dialects: [`current_date`], [`current_time`], [`current_timestamp`];
9//! - SQLite: [`date`], [`time`], [`datetime`], [`strftime`], [`julianday`],
10//!   [`unixepoch`], [`timediff`];
11//! - PostgreSQL: [`now`], [`date_trunc`], [`extract`], [`age`], [`to_char`],
12//!   [`to_timestamp`], [`to_date`], [`to_number`], [`date_bin`],
13//!   [`make_date`], [`make_timestamp`], [`localtime`], [`localtimestamp`],
14//!   [`clock_timestamp`].
15
16use crate::dialect::DialectTypes;
17use crate::dialect::{DialectSupports, feature};
18use crate::sql::{SQL, Token};
19use crate::traits::SQLParam;
20use crate::types::{DataType, Numeric, Temporal, Textual};
21use crate::{PostgresDialect, SQLiteDialect};
22use drizzle_types::postgres::types::{Timestamp as PgTimestamp, Timestamptz as PgTimestamptz};
23
24use super::{AggregateKind, Expr, Nullability, SQLExpr, Scalar};
25
26#[diagnostic::on_unimplemented(
27    message = "DATE_TRUNC output type is not defined for `{Self}` on this dialect",
28    label = "DATE_TRUNC accepts timestamp/timestamptz and preserves the timestamp flavor"
29)]
30/// Temporal types that [`date_trunc`] accepts on dialect `D`, and its result
31/// type. On PostgreSQL, `timestamp` and `timestamptz` are accepted and keep
32/// their type.
33pub trait DateTruncPolicy<D>: Temporal {
34    /// Result type of `DATE_TRUNC`.
35    type Output: DataType;
36}
37
38impl DialectSupports<feature::SQLiteDateTime> for SQLiteDialect {}
39impl DialectSupports<feature::PostgresDateTime> for PostgresDialect {}
40
41impl DateTruncPolicy<PostgresDialect> for PgTimestamptz {
42    type Output = Self;
43}
44impl DateTruncPolicy<PostgresDialect> for PgTimestamp {
45    type Output = Self;
46}
47
48// =============================================================================
49// CURRENT DATE/TIME (Cross-database)
50// =============================================================================
51
52/// The current date (`CURRENT_DATE`), on every dialect.
53///
54/// The result is the dialect's date type and never NULL.
55///
56/// # Examples
57///
58/// ```rust
59/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
60/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
61/// # #[derive(Clone, Debug)] struct Value(String);
62/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
63/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
64/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
65/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
66/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
67/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
68/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
69/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
70/// assert_eq!(current_date::<Value>().sql(), "CURRENT_DATE");
71/// ```
72#[must_use]
73pub fn current_date<'a, V>()
74-> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::Date, super::NonNull, Scalar, ()>
75where
76    V: SQLParam + 'a,
77{
78    SQLExpr::new(SQL::raw("CURRENT_DATE"))
79}
80
81/// The current time (`CURRENT_TIME`), on every dialect.
82///
83/// The result is the dialect's time type and never NULL.
84///
85/// # Examples
86///
87/// ```rust
88/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
89/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
90/// # #[derive(Clone, Debug)] struct Value(String);
91/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
92/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
93/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
94/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
95/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
96/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
97/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
98/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
99/// assert_eq!(current_time::<Value>().sql(), "CURRENT_TIME");
100/// ```
101#[must_use]
102pub fn current_time<'a, V>()
103-> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::Time, super::NonNull, Scalar, ()>
104where
105    V: SQLParam + 'a,
106{
107    SQLExpr::new(SQL::raw("CURRENT_TIME"))
108}
109
110/// The current date and time (`CURRENT_TIMESTAMP`), on every dialect.
111///
112/// The result is the dialect's timestamp-with-time-zone type (text on
113/// SQLite, `timestamptz` on PostgreSQL, `TIMESTAMP` on MySQL) and never NULL.
114///
115/// # Examples
116///
117/// ```rust
118/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
119/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
120/// # #[derive(Clone, Debug)] struct Value(String);
121/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
122/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
123/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
124/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
125/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
126/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
127/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
128/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
129/// assert_eq!(current_timestamp::<Value>().sql(), "CURRENT_TIMESTAMP");
130/// ```
131#[must_use]
132pub fn current_timestamp<'a, V>()
133-> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::TimestampTz, super::NonNull, Scalar, ()>
134where
135    V: SQLParam + 'a,
136{
137    SQLExpr::new(SQL::raw("CURRENT_TIMESTAMP"))
138}
139
140// =============================================================================
141// SQLite-specific DATE/TIME FUNCTIONS
142// =============================================================================
143
144/// The date part of a time value (`DATE`), on SQLite.
145///
146/// The argument must be temporal. The result is the dialect's date type and keeps the
147/// argument's nullability.
148///
149/// # Examples
150///
151/// ```rust
152/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
153/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
154/// # #[derive(Clone, Debug)] struct Value(String);
155/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
156/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
157/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
158/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
159/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
160/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
161/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
162/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
163/// assert_eq!(date(users.created_at).sql(), r#"DATE ("users"."created_at")"#);
164/// ```
165#[allow(clippy::type_complexity)]
166pub fn date<'a, V, E>(
167    expr: E,
168) -> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::Date, E::Nullable, E::Aggregate, E::Sources>
169where
170    V: SQLParam + 'a,
171    V::DialectMarker: DialectSupports<feature::SQLiteDateTime>,
172    E: Expr<'a, V>,
173    E::SQLType: Temporal,
174{
175    SQLExpr::new(SQL::func("DATE", expr.into_sql()))
176}
177
178/// The time part of a time value (`TIME`), on SQLite.
179///
180/// The argument must be temporal. The result is the dialect's time type and keeps the
181/// argument's nullability.
182///
183/// # Examples
184///
185/// ```rust
186/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
187/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
188/// # #[derive(Clone, Debug)] struct Value(String);
189/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
190/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
191/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
192/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
193/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
194/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
195/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
196/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
197/// assert_eq!(time(users.created_at).sql(), r#"TIME ("users"."created_at")"#);
198/// ```
199#[allow(clippy::type_complexity)]
200pub fn time<'a, V, E>(
201    expr: E,
202) -> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::Time, E::Nullable, E::Aggregate, E::Sources>
203where
204    V: SQLParam + 'a,
205    V::DialectMarker: DialectSupports<feature::SQLiteDateTime>,
206    E: Expr<'a, V>,
207    E::SQLType: Temporal,
208{
209    SQLExpr::new(SQL::func("TIME", expr.into_sql()))
210}
211
212/// A time value as `YYYY-MM-DD HH:MM:SS` (`DATETIME`), on SQLite.
213///
214/// The argument must be temporal. The result is the dialect's timestamp type and keeps the
215/// argument's nullability.
216///
217/// # Examples
218///
219/// ```rust
220/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
221/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
222/// # #[derive(Clone, Debug)] struct Value(String);
223/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
224/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
225/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
226/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
227/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
228/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
229/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
230/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
231/// assert_eq!(datetime(users.created_at).sql(), r#"DATETIME ("users"."created_at")"#);
232/// ```
233#[allow(clippy::type_complexity)]
234pub fn datetime<'a, V, E>(
235    expr: E,
236) -> SQLExpr<
237    'a,
238    V,
239    <V::DialectMarker as DialectTypes>::Timestamp,
240    E::Nullable,
241    E::Aggregate,
242    E::Sources,
243>
244where
245    V: SQLParam + 'a,
246    V::DialectMarker: DialectSupports<feature::SQLiteDateTime>,
247    E: Expr<'a, V>,
248    E::SQLType: Temporal,
249{
250    SQLExpr::new(SQL::func("DATETIME", expr.into_sql()))
251}
252
253/// Formats a time value as text (`STRFTIME(format, expr)`), on SQLite.
254///
255/// `format` must be text and `expr` temporal. The result is text and keeps
256/// `expr`'s nullability. Common format codes:
257///
258/// - `%Y` year, `%m` month (01-12), `%d` day (01-31)
259/// - `%H` hour (00-23), `%M` minute, `%S` second
260/// - `%s` Unix time, `%w` weekday (0-6, Sunday is 0), `%j` day of year
261///
262/// # Examples
263///
264/// ```rust
265/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
266/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
267/// # #[derive(Clone, Debug)] struct Value(String);
268/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
269/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
270/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
271/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
272/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
273/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
274/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
275/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
276/// let day = strftime("%Y-%m-%d", users.created_at);
277/// assert_eq!(day.sql(), r#"STRFTIME (?, "users"."created_at")"#);
278/// ```
279#[allow(clippy::type_complexity)]
280pub fn strftime<'a, V, F, E>(
281    format: F,
282    expr: E,
283) -> SQLExpr<
284    'a,
285    V,
286    <V::DialectMarker as DialectTypes>::Text,
287    E::Nullable,
288    <F::Aggregate as AggregateKind>::Or<E::Aggregate>,
289    (F::Sources, E::Sources),
290>
291where
292    V: SQLParam + 'a,
293    V::DialectMarker: DialectSupports<feature::SQLiteDateTime>,
294    F: Expr<'a, V>,
295    F::SQLType: Textual,
296    E: Expr<'a, V>,
297    E::SQLType: Temporal,
298{
299    SQLExpr::new(SQL::func(
300        "STRFTIME",
301        format.into_sql().push(Token::COMMA).append(expr.into_sql()),
302    ))
303}
304
305/// A time value as a Julian day number (`JULIANDAY`), on SQLite.
306///
307/// The argument must be temporal. The result is the dialect's double type and
308/// keeps the argument's nullability.
309///
310/// # Examples
311///
312/// ```rust
313/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
314/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
315/// # #[derive(Clone, Debug)] struct Value(String);
316/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
317/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
318/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
319/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
320/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
321/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
322/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
323/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
324/// assert_eq!(julianday(users.created_at).sql(), r#"JULIANDAY ("users"."created_at")"#);
325/// ```
326#[allow(clippy::type_complexity)]
327pub fn julianday<'a, V, E>(
328    expr: E,
329) -> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::Double, E::Nullable, E::Aggregate, E::Sources>
330where
331    V: SQLParam + 'a,
332    V::DialectMarker: DialectSupports<feature::SQLiteDateTime>,
333    E: Expr<'a, V>,
334    E::SQLType: Temporal,
335{
336    SQLExpr::new(SQL::func("JULIANDAY", expr.into_sql()))
337}
338
339/// A time value as seconds since 1970-01-01 (`UNIXEPOCH`), on SQLite 3.38+.
340///
341/// The argument must be temporal. The result is the dialect's big-integer
342/// type and keeps the argument's nullability.
343///
344/// # Examples
345///
346/// ```rust
347/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
348/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
349/// # #[derive(Clone, Debug)] struct Value(String);
350/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
351/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
352/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
353/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
354/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
355/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
356/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
357/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
358/// assert_eq!(unixepoch(users.created_at).sql(), r#"UNIXEPOCH ("users"."created_at")"#);
359/// ```
360#[allow(clippy::type_complexity)]
361pub fn unixepoch<'a, V, E>(
362    expr: E,
363) -> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::BigInt, E::Nullable, E::Aggregate, E::Sources>
364where
365    V: SQLParam + 'a,
366    V::DialectMarker: DialectSupports<feature::SQLiteDateTime>,
367    E: Expr<'a, V>,
368    E::SQLType: Temporal,
369{
370    SQLExpr::new(SQL::func("UNIXEPOCH", expr.into_sql()))
371}
372
373// =============================================================================
374// PostgreSQL-specific DATE/TIME FUNCTIONS
375// =============================================================================
376
377/// The current date and time (`NOW()`), on PostgreSQL.
378///
379/// The result is `timestamptz` and never NULL. It is fixed for the whole
380/// transaction; see [`clock_timestamp`] for the wall-clock time.
381///
382/// # Examples
383///
384/// ```rust
385/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
386/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
387/// # #[derive(Clone, Debug)] struct Value(String);
388/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
389/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
390/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
391/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
392/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
393/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
394/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
395/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
396/// assert_eq!(now::<Value>().sql(), "NOW()");
397/// ```
398#[must_use]
399pub fn now<'a, V>()
400-> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::TimestampTz, super::NonNull, Scalar, ()>
401where
402    V: SQLParam + 'a,
403    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
404{
405    SQLExpr::new(SQL::raw("NOW()"))
406}
407
408/// Truncates a timestamp to a unit (`DATE_TRUNC(unit, expr)`), on PostgreSQL.
409///
410/// `unit` is text such as `'hour'`, `'day'`, `'week'`, `'month'` or `'year'`.
411/// `expr` must be `timestamp` or `timestamptz` (see [`DateTruncPolicy`]); the
412/// result has the same type and keeps `expr`'s nullability.
413///
414/// # Examples
415///
416/// ```rust
417/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
418/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
419/// # #[derive(Clone, Debug)] struct Value(String);
420/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
421/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
422/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
423/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
424/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
425/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
426/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
427/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
428/// let month = date_trunc("month", users.created_at);
429/// assert_eq!(month.sql(), r#"DATE_TRUNC ($1, "users"."created_at")"#);
430/// ```
431#[allow(clippy::type_complexity)]
432pub fn date_trunc<'a, V, P, E>(
433    precision: P,
434    expr: E,
435) -> SQLExpr<
436    'a,
437    V,
438    <E::SQLType as DateTruncPolicy<V::DialectMarker>>::Output,
439    E::Nullable,
440    <P::Aggregate as AggregateKind>::Or<E::Aggregate>,
441    (P::Sources, E::Sources),
442>
443where
444    V: SQLParam + 'a,
445    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
446    P: Expr<'a, V>,
447    P::SQLType: Textual,
448    E: Expr<'a, V>,
449    E::SQLType: DateTruncPolicy<V::DialectMarker>,
450{
451    SQLExpr::new(SQL::func(
452        "DATE_TRUNC",
453        precision
454            .into_sql()
455            .push(Token::COMMA)
456            .append(expr.into_sql()),
457    ))
458}
459
460/// One field of a time value (`EXTRACT(field FROM expr)`), on PostgreSQL.
461///
462/// `field` is written into the SQL as is, so pass a fixed field name such as
463/// `"YEAR"`, `"MONTH"`, `"DAY"`, `"HOUR"`, `"DOW"` or `"EPOCH"`, never user
464/// input. `expr` must be temporal. PostgreSQL 14+ returns `numeric`, so the
465/// call is cast to `DOUBLE PRECISION`; the result is `float8` and keeps
466/// `expr`'s nullability.
467///
468/// # Examples
469///
470/// ```rust
471/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
472/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
473/// # #[derive(Clone, Debug)] struct Value(String);
474/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
475/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
476/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
477/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
478/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
479/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
480/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
481/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
482/// let year = extract("YEAR", users.created_at);
483/// assert_eq!(
484///     year.sql(),
485///     r#"CAST (EXTRACT( YEAR FROM "users"."created_at") AS DOUBLE PRECISION)"#
486/// );
487/// ```
488#[allow(clippy::type_complexity)]
489pub fn extract<'a, 'f, V, E>(
490    field: &'f str,
491    expr: E,
492) -> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::Double, E::Nullable, E::Aggregate, E::Sources>
493where
494    'f: 'a,
495    V: SQLParam + 'a,
496    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
497    E: Expr<'a, V>,
498    E::SQLType: Temporal,
499{
500    // EXTRACT uses special syntax: EXTRACT(field FROM timestamp). PostgreSQL 14+
501    // returns NUMERIC, so the result is cast to the declared double type.
502    let extracted = SQL::raw("EXTRACT(")
503        .append(SQL::raw(field))
504        .append(SQL::raw(" FROM "))
505        .append(expr.into_sql())
506        .push(Token::RPAREN);
507    SQLExpr::new(SQL::func(
508        "CAST",
509        extracted
510            .push(Token::AS)
511            .append(SQL::raw("DOUBLE PRECISION")),
512    ))
513}
514
515/// The interval between two timestamps (`AGE(a, b)`, that is `a - b`), on PostgreSQL.
516///
517/// Both arguments must be temporal. The result is `interval`, nullable if
518/// either argument is.
519///
520/// # Examples
521///
522/// ```rust
523/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
524/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
525/// # #[derive(Clone, Debug)] struct Value(String);
526/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
527/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
528/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
529/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
530/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
531/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
532/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
533/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
534/// let account_age = age(now(), users.created_at);
535/// assert_eq!(account_age.sql(), r#"AGE (NOW(), "users"."created_at")"#);
536/// ```
537#[allow(clippy::type_complexity)]
538pub fn age<'a, V, E1, E2>(
539    timestamp1: E1,
540    timestamp2: E2,
541) -> SQLExpr<
542    'a,
543    V,
544    drizzle_types::postgres::types::Interval,
545    <E1::Nullable as Nullability>::Or<E2::Nullable>,
546    <E1::Aggregate as AggregateKind>::Or<E2::Aggregate>,
547    (E1::Sources, E2::Sources),
548>
549where
550    V: SQLParam + 'a,
551    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
552    E1: Expr<'a, V>,
553    E1::SQLType: Temporal,
554    E2: Expr<'a, V>,
555    E2::SQLType: Temporal,
556    E2::Nullable: Nullability,
557{
558    SQLExpr::new(SQL::func(
559        "AGE",
560        timestamp1
561            .into_sql()
562            .push(Token::COMMA)
563            .append(timestamp2.into_sql()),
564    ))
565}
566
567/// Formats a time value as text (`TO_CHAR(expr, format)`), on PostgreSQL.
568///
569/// `expr` must be temporal and `format` text. The result is text and keeps
570/// `expr`'s nullability. Common patterns: `YYYY`, `MM`, `DD`, `HH24`, `MI`,
571/// `SS`, `Day`, `Month`.
572///
573/// # Examples
574///
575/// ```rust
576/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
577/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
578/// # #[derive(Clone, Debug)] struct Value(String);
579/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
580/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
581/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
582/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
583/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
584/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
585/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
586/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
587/// let day = to_char(users.created_at, "YYYY-MM-DD");
588/// assert_eq!(day.sql(), r#"TO_CHAR ("users"."created_at", $1)"#);
589/// ```
590#[allow(clippy::type_complexity)]
591pub fn to_char<'a, V, E, F>(
592    expr: E,
593    format: F,
594) -> SQLExpr<
595    'a,
596    V,
597    <V::DialectMarker as DialectTypes>::Text,
598    E::Nullable,
599    <E::Aggregate as AggregateKind>::Or<F::Aggregate>,
600    (E::Sources, F::Sources),
601>
602where
603    V: SQLParam + 'a,
604    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
605    E: Expr<'a, V>,
606    E::SQLType: Temporal,
607    F: Expr<'a, V>,
608    F::SQLType: Textual,
609{
610    SQLExpr::new(SQL::func(
611        "TO_CHAR",
612        expr.into_sql().push(Token::COMMA).append(format.into_sql()),
613    ))
614}
615
616/// Converts Unix time in seconds to a timestamp (`TO_TIMESTAMP`), on PostgreSQL.
617///
618/// The argument must be numeric. The result is `timestamptz` and keeps the
619/// argument's nullability.
620///
621/// # Examples
622///
623/// ```rust
624/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
625/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
626/// # #[derive(Clone, Debug)] struct Value(String);
627/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
628/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
629/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
630/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
631/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
632/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
633/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
634/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
635/// assert_eq!(to_timestamp(users.age).sql(), r#"TO_TIMESTAMP ("users"."age")"#);
636/// ```
637pub fn to_timestamp<'a, V, E>(
638    expr: E,
639) -> SQLExpr<'a, V, PgTimestamptz, E::Nullable, E::Aggregate, E::Sources>
640where
641    V: SQLParam + 'a,
642    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
643    E: Expr<'a, V>,
644    E::SQLType: Numeric,
645{
646    SQLExpr::new(SQL::func("TO_TIMESTAMP", expr.into_sql()))
647}
648
649// =============================================================================
650// Additional PostgreSQL Formatting Functions
651// =============================================================================
652
653/// Parses text into a date using a format (`TO_DATE(text, format)`), on PostgreSQL.
654///
655/// Both arguments must be text. The result is `date` and keeps the first
656/// argument's nullability.
657///
658/// # Examples
659///
660/// ```rust
661/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
662/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
663/// # #[derive(Clone, Debug)] struct Value(String);
664/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
665/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
666/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
667/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
668/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
669/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
670/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
671/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
672/// let d = to_date::<Value, _, _>("2024-01-15", "YYYY-MM-DD");
673/// assert_eq!(d.sql(), "TO_DATE ($1, $2)");
674/// ```
675#[allow(clippy::type_complexity)]
676pub fn to_date<'a, V, E, F>(
677    expr: E,
678    format: F,
679) -> SQLExpr<
680    'a,
681    V,
682    <V::DialectMarker as DialectTypes>::Date,
683    E::Nullable,
684    <E::Aggregate as AggregateKind>::Or<F::Aggregate>,
685    (E::Sources, F::Sources),
686>
687where
688    V: SQLParam + 'a,
689    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
690    E: Expr<'a, V>,
691    E::SQLType: Textual,
692    F: Expr<'a, V>,
693    F::SQLType: Textual,
694{
695    SQLExpr::new(SQL::func(
696        "TO_DATE",
697        expr.into_sql().push(Token::COMMA).append(format.into_sql()),
698    ))
699}
700
701/// Parses text into a number using a format (`TO_NUMBER(text, format)`), on PostgreSQL.
702///
703/// Both arguments must be text. The result is `numeric` and keeps the first
704/// argument's nullability.
705///
706/// # Examples
707///
708/// ```rust
709/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
710/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
711/// # #[derive(Clone, Debug)] struct Value(String);
712/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
713/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
714/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
715/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
716/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
717/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
718/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
719/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
720/// let n = to_number(users.name, "9G999D99");
721/// assert_eq!(n.sql(), r#"TO_NUMBER ("users"."name", $1)"#);
722/// ```
723#[allow(clippy::type_complexity)]
724pub fn to_number<'a, V, E, F>(
725    expr: E,
726    format: F,
727) -> SQLExpr<
728    'a,
729    V,
730    drizzle_types::postgres::types::Numeric,
731    E::Nullable,
732    <E::Aggregate as AggregateKind>::Or<F::Aggregate>,
733    (E::Sources, F::Sources),
734>
735where
736    V: SQLParam + 'a,
737    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
738    E: Expr<'a, V>,
739    E::SQLType: Textual,
740    F: Expr<'a, V>,
741    F::SQLType: Textual,
742{
743    SQLExpr::new(SQL::func(
744        "TO_NUMBER",
745        expr.into_sql().push(Token::COMMA).append(format.into_sql()),
746    ))
747}
748
749// =============================================================================
750// DATE_BIN (PostgreSQL 14+)
751// =============================================================================
752
753/// Rounds a timestamp down into fixed-size buckets (`DATE_BIN`), on PostgreSQL 14+.
754///
755/// Buckets are `stride` wide (an interval as text, such as `'15 minutes'`) and
756/// start at `origin`. `source` and `origin` must be temporal. The stride is
757/// cast to `INTERVAL`. The result has `source`'s type and is nullable if any
758/// argument is.
759///
760/// # Examples
761///
762/// ```rust
763/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
764/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
765/// # #[derive(Clone, Debug)] struct Value(String);
766/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
767/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
768/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
769/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
770/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
771/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
772/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
773/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
774/// let bucket = date_bin("15 minutes", users.created_at, localtimestamp());
775/// assert_eq!(
776///     bucket.sql(),
777///     r#"DATE_BIN (CAST ($1 AS INTERVAL), "users"."created_at", LOCALTIMESTAMP)"#
778/// );
779/// ```
780#[allow(clippy::type_complexity)]
781pub fn date_bin<'a, V, S, E, O>(
782    stride: S,
783    source: E,
784    origin: O,
785) -> SQLExpr<
786    'a,
787    V,
788    E::SQLType,
789    <<S::Nullable as Nullability>::Or<E::Nullable> as Nullability>::Or<O::Nullable>,
790    <<S::Aggregate as AggregateKind>::Or<E::Aggregate> as AggregateKind>::Or<O::Aggregate>,
791    (S::Sources, (E::Sources, O::Sources)),
792>
793where
794    V: SQLParam + 'a,
795    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
796    S: Expr<'a, V>,
797    E: Expr<'a, V>,
798    E::SQLType: Temporal,
799    O: Expr<'a, V>,
800    O::SQLType: Temporal,
801    E::Nullable: Nullability,
802    O::Nullable: Nullability,
803    O::Aggregate: super::AggregateKind,
804{
805    // The stride parameter binds as text; PostgreSQL resolves the overload at
806    // prepare time, so the cast keeps the bound and inferred types aligned.
807    SQLExpr::new(SQL::func(
808        "DATE_BIN",
809        super::math::pg_cast(stride.into_sql(), "INTERVAL")
810            .push(Token::COMMA)
811            .append(source.into_sql())
812            .push(Token::COMMA)
813            .append(origin.into_sql()),
814    ))
815}
816
817// =============================================================================
818// MAKE_DATE / MAKE_TIMESTAMP (PostgreSQL)
819// =============================================================================
820
821/// Builds a date from year, month and day (`MAKE_DATE`), on PostgreSQL.
822///
823/// All arguments must be numeric. The result is `date`, nullable if any
824/// argument is.
825///
826/// # Examples
827///
828/// ```rust
829/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
830/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
831/// # #[derive(Clone, Debug)] struct Value(String);
832/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
833/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
834/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
835/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
836/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
837/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
838/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
839/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
840/// let d = make_date::<Value, _, _, _>(2024, 1, 15);
841/// assert_eq!(d.sql(), "MAKE_DATE ($1, $2, $3)");
842/// ```
843#[allow(clippy::type_complexity)]
844pub fn make_date<'a, V, Y, M, D>(
845    year: Y,
846    month: M,
847    day: D,
848) -> SQLExpr<
849    'a,
850    V,
851    <V::DialectMarker as DialectTypes>::Date,
852    <<Y::Nullable as Nullability>::Or<M::Nullable> as Nullability>::Or<D::Nullable>,
853    <<Y::Aggregate as AggregateKind>::Or<M::Aggregate> as AggregateKind>::Or<D::Aggregate>,
854    (Y::Sources, (M::Sources, D::Sources)),
855>
856where
857    V: SQLParam + 'a,
858    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
859    Y: Expr<'a, V>,
860    Y::SQLType: Numeric,
861    M: Expr<'a, V>,
862    M::SQLType: Numeric,
863    D: Expr<'a, V>,
864    D::SQLType: Numeric,
865    M::Nullable: Nullability,
866    D::Nullable: Nullability,
867    D::Aggregate: super::AggregateKind,
868{
869    SQLExpr::new(SQL::func(
870        "MAKE_DATE",
871        year.into_sql()
872            .push(Token::COMMA)
873            .append(month.into_sql())
874            .push(Token::COMMA)
875            .append(day.into_sql()),
876    ))
877}
878
879/// Builds a timestamp from its parts (`MAKE_TIMESTAMP`), on PostgreSQL.
880///
881/// Takes year, month, day, hour, minute and seconds; all must be numeric
882/// (seconds may have a fraction). The result is `timestamp`, nullable if any
883/// argument is.
884///
885/// # Examples
886///
887/// ```rust
888/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
889/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
890/// # #[derive(Clone, Debug)] struct Value(String);
891/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
892/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
893/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
894/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
895/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
896/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
897/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
898/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
899/// let ts = make_timestamp::<Value, _, _, _, _, _, _>(2024, 1, 15, 10, 30, 0.0);
900/// assert_eq!(ts.sql(), "MAKE_TIMESTAMP ($1, $2, $3, $4, $5, $6)");
901/// ```
902#[allow(clippy::type_complexity)]
903pub fn make_timestamp<'a, V, Y, Mo, D, H, Mi, S>(
904    year: Y,
905    month: Mo,
906    day: D,
907    hour: H,
908    minute: Mi,
909    second: S,
910) -> SQLExpr<
911    'a,
912    V,
913    <V::DialectMarker as DialectTypes>::Timestamp,
914    <<<<Y::Nullable as Nullability>::Or<Mo::Nullable> as Nullability>::Or<D::Nullable> as Nullability>::Or<H::Nullable,> as Nullability>::Or<<Mi::Nullable as Nullability>::Or<S::Nullable>>,
915    <<<<Y::Aggregate as AggregateKind>::Or<Mo::Aggregate> as AggregateKind>::Or<D::Aggregate> as AggregateKind>::Or<H::Aggregate,> as AggregateKind>::Or<<Mi::Aggregate as AggregateKind>::Or<S::Aggregate>>,
916    (
917        Y::Sources,
918        (
919            Mo::Sources,
920            (D::Sources, (H::Sources, (Mi::Sources, S::Sources))),
921        ),
922    ),
923>
924where
925    V: SQLParam + 'a,
926    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
927    Y: Expr<'a, V>,
928    Y::SQLType: Numeric,
929    Mo: Expr<'a, V>,
930    Mo::SQLType: Numeric,
931    D: Expr<'a, V>,
932    D::SQLType: Numeric,
933    H: Expr<'a, V>,
934    H::SQLType: Numeric,
935    Mi: Expr<'a, V>,
936    Mi::SQLType: Numeric,
937    S: Expr<'a, V>,
938    S::SQLType: Numeric,
939    H::Nullable: Nullability,
940    D::Nullable: Nullability,
941    Mo::Nullable: Nullability,
942    S::Nullable: Nullability,
943    H::Aggregate: super::AggregateKind,
944    D::Aggregate: super::AggregateKind,
945    S::Aggregate: super::AggregateKind,
946{
947    SQLExpr::new(SQL::func(
948        "MAKE_TIMESTAMP",
949        year.into_sql()
950            .push(Token::COMMA)
951            .append(month.into_sql())
952            .push(Token::COMMA)
953            .append(day.into_sql())
954            .push(Token::COMMA)
955            .append(hour.into_sql())
956            .push(Token::COMMA)
957            .append(minute.into_sql())
958            .push(Token::COMMA)
959            .append(second.into_sql()),
960    ))
961}
962
963// =============================================================================
964// Current Time (PostgreSQL-specific)
965// =============================================================================
966
967/// The current time without time zone (`LOCALTIME`), on PostgreSQL.
968///
969/// The result is `time` and never NULL.
970///
971/// # Examples
972///
973/// ```rust
974/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
975/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
976/// # #[derive(Clone, Debug)] struct Value(String);
977/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
978/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
979/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
980/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
981/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
982/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
983/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
984/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
985/// assert_eq!(localtime::<Value>().sql(), "LOCALTIME");
986/// ```
987#[must_use]
988pub fn localtime<'a, V>()
989-> SQLExpr<'a, V, <V::DialectMarker as DialectTypes>::Time, super::NonNull, Scalar, ()>
990where
991    V: SQLParam + 'a,
992    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
993{
994    SQLExpr::new(SQL::raw("LOCALTIME"))
995}
996
997/// The current date and time without time zone (`LOCALTIMESTAMP`), on PostgreSQL.
998///
999/// The result is `timestamp` and never NULL.
1000///
1001/// # Examples
1002///
1003/// ```rust
1004/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
1005/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
1006/// # #[derive(Clone, Debug)] struct Value(String);
1007/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
1008/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
1009/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
1010/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
1011/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
1012/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
1013/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
1014/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
1015/// assert_eq!(localtimestamp::<Value>().sql(), "LOCALTIMESTAMP");
1016/// ```
1017#[must_use]
1018pub fn localtimestamp<'a, V>() -> SQLExpr<'a, V, PgTimestamp, super::NonNull, Scalar, ()>
1019where
1020    V: SQLParam + 'a,
1021    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
1022{
1023    SQLExpr::new(SQL::raw("LOCALTIMESTAMP"))
1024}
1025
1026/// The actual wall-clock time (`CLOCK_TIMESTAMP()`), on PostgreSQL.
1027///
1028/// Unlike [`now`] and `CURRENT_TIMESTAMP`, the value changes during a
1029/// transaction. The result is `timestamptz` and never NULL.
1030///
1031/// # Examples
1032///
1033/// ```rust
1034/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
1035/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
1036/// # #[derive(Clone, Debug)] struct Value(String);
1037/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
1038/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
1039/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
1040/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
1041/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
1042/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
1043/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
1044/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
1045/// assert_eq!(clock_timestamp::<Value>().sql(), "CLOCK_TIMESTAMP()");
1046/// ```
1047#[must_use]
1048pub fn clock_timestamp<'a, V>() -> SQLExpr<'a, V, PgTimestamptz, super::NonNull, Scalar, ()>
1049where
1050    V: SQLParam + 'a,
1051    V::DialectMarker: DialectSupports<feature::PostgresDateTime>,
1052{
1053    SQLExpr::new(SQL::raw("CLOCK_TIMESTAMP()"))
1054}
1055
1056// =============================================================================
1057// TIMEDIFF (SQLite 3.43+)
1058// =============================================================================
1059
1060/// The difference between two time values as text (`TIMEDIFF`), on SQLite 3.43+.
1061///
1062/// Both arguments must be temporal. The result is text in the form
1063/// `+YYYY-MM-DD HH:MM:SS.SSS`, nullable if either argument is.
1064///
1065/// # Examples
1066///
1067/// ```rust
1068/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
1069/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
1070/// # #[derive(Clone, Debug)] struct Value(String);
1071/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
1072/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
1073/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
1074/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
1075/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
1076/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
1077/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
1078/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
1079/// let since = timediff(current_timestamp(), users.created_at);
1080/// assert_eq!(since.sql(), r#"TIMEDIFF (CURRENT_TIMESTAMP, "users"."created_at")"#);
1081/// ```
1082#[allow(clippy::type_complexity)]
1083pub fn timediff<'a, V, E1, E2>(
1084    time1: E1,
1085    time2: E2,
1086) -> SQLExpr<
1087    'a,
1088    V,
1089    <V::DialectMarker as DialectTypes>::Text,
1090    <E1::Nullable as Nullability>::Or<E2::Nullable>,
1091    <E1::Aggregate as AggregateKind>::Or<E2::Aggregate>,
1092    (E1::Sources, E2::Sources),
1093>
1094where
1095    V: SQLParam + 'a,
1096    V::DialectMarker: DialectSupports<feature::SQLiteDateTime>,
1097    E1: Expr<'a, V>,
1098    E1::SQLType: Temporal,
1099    E2: Expr<'a, V>,
1100    E2::SQLType: Temporal,
1101    E2::Nullable: Nullability,
1102{
1103    SQLExpr::new(SQL::func(
1104        "TIMEDIFF",
1105        time1.into_sql().push(Token::COMMA).append(time2.into_sql()),
1106    ))
1107}
drizzle_core/expr/datetime.rs

drizzle_core/expr/
datetime.rs
