mz_expr/scalar/func/impls/
timestamp.rs

1// Copyright Materialize, Inc. and contributors. All rights reserved.
2//
3// Use of this software is governed by the Business Source License
4// included in the LICENSE file.
5//
6// As of the Change Date specified in that file, in accordance with
7// the Business Source License, use of this software will be governed
8// by the Apache License, Version 2.0.
9
10use std::fmt;
11
12use chrono::{
13    DateTime, Duration, FixedOffset, NaiveDateTime, NaiveTime, Offset, TimeZone, Timelike, Utc,
14};
15use mz_expr_derive::sqlfunc;
16use mz_lowertest::MzReflect;
17use mz_ore::result::ResultExt;
18use mz_pgtz::timezone::{Timezone, TimezoneSpec};
19use mz_repr::adt::date::Date;
20use mz_repr::adt::datetime::DateTimeUnits;
21use mz_repr::adt::interval::Interval;
22use mz_repr::adt::numeric::{DecimalLike, Numeric};
23use mz_repr::adt::timestamp::{CheckedTimestamp, MAX_PRECISION, TimestampPrecision};
24use mz_repr::{SqlColumnType, SqlScalarType, strconv};
25use serde::{Deserialize, Serialize};
26
27use crate::EvalError;
28use crate::func::parse_timezone;
29use crate::scalar::func::format::DateTimeFormat;
30use crate::scalar::func::{EagerUnaryFunc, TimestampLike};
31
32#[sqlfunc(
33    sqlname = "timestamp_to_text",
34    preserves_uniqueness = true,
35    inverse = to_unary!(super::CastStringToTimestamp(None))
36)]
37fn cast_timestamp_to_string(a: CheckedTimestamp<NaiveDateTime>) -> String {
38    let mut buf = String::new();
39    strconv::format_timestamp(&mut buf, &a);
40    buf
41}
42
43#[sqlfunc(
44    sqlname = "timestamp_with_time_zone_to_text",
45    preserves_uniqueness = true,
46    inverse = to_unary!(super::CastStringToTimestampTz(None))
47)]
48fn cast_timestamp_tz_to_string(a: CheckedTimestamp<DateTime<Utc>>) -> String {
49    let mut buf = String::new();
50    strconv::format_timestamptz(&mut buf, &a);
51    buf
52}
53
54#[sqlfunc(
55    sqlname = "timestamp_to_date",
56    preserves_uniqueness = false,
57    inverse = to_unary!(super::CastDateToTimestamp(None)),
58    is_monotone = true
59)]
60fn cast_timestamp_to_date(a: CheckedTimestamp<NaiveDateTime>) -> Result<Date, EvalError> {
61    Ok(a.date().try_into()?)
62}
63
64#[sqlfunc(
65    sqlname = "timestamp_with_time_zone_to_date",
66    preserves_uniqueness = false,
67    inverse = to_unary!(super::CastDateToTimestampTz(None)),
68    is_monotone = true
69)]
70fn cast_timestamp_tz_to_date(a: CheckedTimestamp<DateTime<Utc>>) -> Result<Date, EvalError> {
71    Ok(a.naive_utc().date().try_into()?)
72}
73
74#[derive(
75    Ord,
76    PartialOrd,
77    Clone,
78    Debug,
79    Eq,
80    PartialEq,
81    Serialize,
82    Deserialize,
83    Hash,
84    MzReflect
85)]
86pub struct CastTimestampToTimestampTz {
87    pub from: Option<TimestampPrecision>,
88    pub to: Option<TimestampPrecision>,
89}
90
91impl EagerUnaryFunc for CastTimestampToTimestampTz {
92    type Input<'a> = CheckedTimestamp<NaiveDateTime>;
93    type Output<'a> = Result<CheckedTimestamp<DateTime<Utc>>, EvalError>;
94
95    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
96        let out =
97            CheckedTimestamp::try_from(DateTime::<Utc>::from_naive_utc_and_offset(a.into(), Utc))?;
98        let updated = out.round_to_precision(self.to)?;
99        Ok(updated)
100    }
101
102    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
103        SqlScalarType::TimestampTz { precision: self.to }.nullable(input.nullable)
104    }
105
106    fn preserves_uniqueness(&self) -> bool {
107        let to_p = self.to.map(|p| p.into_u8()).unwrap_or(MAX_PRECISION);
108        let from_p = self.from.map(|p| p.into_u8()).unwrap_or(MAX_PRECISION);
109        // If it's getting cast to a higher precision, it should preserve uniqueness but not otherwise.
110        to_p >= from_p
111    }
112
113    fn inverse(&self) -> Option<crate::UnaryFunc> {
114        to_unary!(super::CastTimestampTzToTimestamp {
115            from: self.from,
116            to: self.to
117        })
118    }
119
120    fn is_monotone(&self) -> bool {
121        true
122    }
123}
124
125impl fmt::Display for CastTimestampToTimestampTz {
126    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
127        f.write_str("timestamp_to_timestamp_with_time_zone")
128    }
129}
130
131#[derive(
132    Ord,
133    PartialOrd,
134    Clone,
135    Debug,
136    Eq,
137    PartialEq,
138    Serialize,
139    Deserialize,
140    Hash,
141    MzReflect
142)]
143pub struct AdjustTimestampPrecision {
144    pub from: Option<TimestampPrecision>,
145    pub to: Option<TimestampPrecision>,
146}
147
148impl EagerUnaryFunc for AdjustTimestampPrecision {
149    type Input<'a> = CheckedTimestamp<NaiveDateTime>;
150    type Output<'a> = Result<CheckedTimestamp<NaiveDateTime>, EvalError>;
151
152    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
153        // This should never have been called if precisions are same.
154        // Adding a soft-assert to flag if there are such instances.
155        mz_ore::soft_assert_no_log!(self.to != self.from);
156
157        let updated = a.round_to_precision(self.to)?;
158        Ok(updated)
159    }
160
161    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
162        SqlScalarType::Timestamp { precision: self.to }.nullable(input.nullable)
163    }
164
165    fn preserves_uniqueness(&self) -> bool {
166        let to_p = self.to.map(|p| p.into_u8()).unwrap_or(MAX_PRECISION);
167        let from_p = self.from.map(|p| p.into_u8()).unwrap_or(MAX_PRECISION);
168        // If it's getting cast to a higher precision, it should preserve uniqueness but not otherwise.
169        to_p >= from_p
170    }
171
172    fn inverse(&self) -> Option<crate::UnaryFunc> {
173        None
174    }
175
176    fn is_monotone(&self) -> bool {
177        true
178    }
179}
180
181impl fmt::Display for AdjustTimestampPrecision {
182    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
183        f.write_str("adjust_timestamp_precision")
184    }
185}
186
187#[derive(
188    Ord,
189    PartialOrd,
190    Clone,
191    Debug,
192    Eq,
193    PartialEq,
194    Serialize,
195    Deserialize,
196    Hash,
197    MzReflect
198)]
199pub struct CastTimestampTzToTimestamp {
200    pub from: Option<TimestampPrecision>,
201    pub to: Option<TimestampPrecision>,
202}
203
204impl EagerUnaryFunc for CastTimestampTzToTimestamp {
205    type Input<'a> = CheckedTimestamp<DateTime<Utc>>;
206    type Output<'a> = Result<CheckedTimestamp<NaiveDateTime>, EvalError>;
207
208    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
209        let out = CheckedTimestamp::try_from(a.naive_utc())?;
210        let updated = out.round_to_precision(self.to)?;
211        Ok(updated)
212    }
213
214    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
215        SqlScalarType::Timestamp { precision: self.to }.nullable(input.nullable)
216    }
217
218    fn preserves_uniqueness(&self) -> bool {
219        let to_p = self.to.map(|p| p.into_u8()).unwrap_or(MAX_PRECISION);
220        let from_p = self.from.map(|p| p.into_u8()).unwrap_or(MAX_PRECISION);
221        // If it's getting cast to a higher precision, it should preserve uniqueness but not otherwise.
222        to_p >= from_p
223    }
224
225    fn inverse(&self) -> Option<crate::UnaryFunc> {
226        to_unary!(super::CastTimestampToTimestampTz {
227            from: self.from,
228            to: self.to
229        })
230    }
231
232    fn is_monotone(&self) -> bool {
233        true
234    }
235}
236
237impl fmt::Display for CastTimestampTzToTimestamp {
238    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
239        f.write_str("timestamp_with_time_zone_to_timestamp")
240    }
241}
242
243#[derive(
244    Ord,
245    PartialOrd,
246    Clone,
247    Debug,
248    Eq,
249    PartialEq,
250    Serialize,
251    Deserialize,
252    Hash,
253    MzReflect
254)]
255pub struct AdjustTimestampTzPrecision {
256    pub from: Option<TimestampPrecision>,
257    pub to: Option<TimestampPrecision>,
258}
259
260impl EagerUnaryFunc for AdjustTimestampTzPrecision {
261    type Input<'a> = CheckedTimestamp<DateTime<Utc>>;
262    type Output<'a> = Result<CheckedTimestamp<DateTime<Utc>>, EvalError>;
263
264    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
265        // This should never have been called if precisions are same.
266        // Adding a soft-assert to flag if there are such instances.
267        mz_ore::soft_assert_no_log!(self.to != self.from);
268
269        let updated = a.round_to_precision(self.to)?;
270        Ok(updated)
271    }
272
273    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
274        SqlScalarType::TimestampTz { precision: self.to }.nullable(input.nullable)
275    }
276
277    fn preserves_uniqueness(&self) -> bool {
278        let to_p = self.to.map(|p| p.into_u8()).unwrap_or(MAX_PRECISION);
279        let from_p = self.from.map(|p| p.into_u8()).unwrap_or(MAX_PRECISION);
280        // If it's getting cast to a higher precision, it should preserve uniqueness but not otherwise.
281        to_p >= from_p
282    }
283
284    fn inverse(&self) -> Option<crate::UnaryFunc> {
285        None
286    }
287
288    fn is_monotone(&self) -> bool {
289        true
290    }
291}
292
293impl fmt::Display for AdjustTimestampTzPrecision {
294    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
295        f.write_str("adjust_timestamp_with_time_zone_precision")
296    }
297}
298
299#[sqlfunc(sqlname = "timestamp_to_time", preserves_uniqueness = false)]
300fn cast_timestamp_to_time(a: CheckedTimestamp<NaiveDateTime>) -> NaiveTime {
301    a.time()
302}
303
304#[sqlfunc(
305    sqlname = "timestamp_with_time_zone_to_time",
306    preserves_uniqueness = false
307)]
308fn cast_timestamp_tz_to_time(a: CheckedTimestamp<DateTime<Utc>>) -> NaiveTime {
309    a.naive_utc().time()
310}
311
312pub fn date_part_interval_inner<D>(units: DateTimeUnits, interval: Interval) -> Result<D, EvalError>
313where
314    D: DecimalLike,
315{
316    match units {
317        DateTimeUnits::Epoch => Ok(interval.as_epoch_seconds()),
318        DateTimeUnits::Millennium => Ok(D::from(interval.millennia())),
319        DateTimeUnits::Century => Ok(D::from(interval.centuries())),
320        DateTimeUnits::Decade => Ok(D::from(interval.decades())),
321        DateTimeUnits::Year => Ok(D::from(interval.years())),
322        DateTimeUnits::Quarter => Ok(D::from(interval.quarters())),
323        DateTimeUnits::Month => Ok(D::from(interval.months())),
324        DateTimeUnits::Day => Ok(D::lossy_from(interval.days())),
325        DateTimeUnits::Hour => Ok(D::lossy_from(interval.hours())),
326        DateTimeUnits::Minute => Ok(D::lossy_from(interval.minutes())),
327        DateTimeUnits::Second => Ok(interval.seconds()),
328        DateTimeUnits::Milliseconds => Ok(interval.milliseconds()),
329        DateTimeUnits::Microseconds => Ok(interval.microseconds()),
330        DateTimeUnits::Week
331        | DateTimeUnits::Timezone
332        | DateTimeUnits::TimezoneHour
333        | DateTimeUnits::TimezoneMinute
334        | DateTimeUnits::DayOfWeek
335        | DateTimeUnits::DayOfYear
336        | DateTimeUnits::IsoDayOfWeek
337        | DateTimeUnits::IsoDayOfYear => Err(EvalError::Unsupported {
338            feature: format!("'{}' timestamp units", units).into(),
339            discussion_no: None,
340        }),
341    }
342}
343
344#[derive(
345    Ord,
346    PartialOrd,
347    Clone,
348    Debug,
349    Eq,
350    PartialEq,
351    Serialize,
352    Deserialize,
353    Hash,
354    MzReflect
355)]
356pub struct ExtractInterval(pub DateTimeUnits);
357
358impl EagerUnaryFunc for ExtractInterval {
359    type Input<'a> = Interval;
360    type Output<'a> = Result<Numeric, EvalError>;
361
362    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
363        date_part_interval_inner(self.0, a)
364    }
365
366    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
367        SqlScalarType::Numeric { max_scale: None }.nullable(input.nullable)
368    }
369}
370
371impl fmt::Display for ExtractInterval {
372    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
373        write!(f, "extract_{}_iv", self.0)
374    }
375}
376
377#[derive(
378    Ord,
379    PartialOrd,
380    Clone,
381    Debug,
382    Eq,
383    PartialEq,
384    Serialize,
385    Deserialize,
386    Hash,
387    MzReflect
388)]
389pub struct DatePartInterval(pub DateTimeUnits);
390
391impl EagerUnaryFunc for DatePartInterval {
392    type Input<'a> = Interval;
393    type Output<'a> = Result<f64, EvalError>;
394
395    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
396        date_part_interval_inner(self.0, a)
397    }
398
399    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
400        SqlScalarType::Float64.nullable(input.nullable)
401    }
402}
403
404impl fmt::Display for DatePartInterval {
405    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
406        write!(f, "date_part_{}_iv", self.0)
407    }
408}
409
410pub fn date_part_timestamp_inner<T, D>(units: DateTimeUnits, ts: &T) -> Result<D, EvalError>
411where
412    T: TimestampLike,
413    D: DecimalLike,
414{
415    match units {
416        DateTimeUnits::Epoch => Ok(TimestampLike::extract_epoch(ts)),
417        DateTimeUnits::Millennium => Ok(D::from(ts.millennium())),
418        DateTimeUnits::Century => Ok(D::from(ts.century())),
419        DateTimeUnits::Decade => Ok(D::from(ts.decade())),
420        DateTimeUnits::Year => Ok(D::from(ts.year())),
421        DateTimeUnits::Quarter => Ok(D::from(ts.quarter())),
422        DateTimeUnits::Week => Ok(D::from(ts.iso_week_number())),
423        DateTimeUnits::Month => Ok(D::from(ts.month())),
424        DateTimeUnits::Day => Ok(D::from(ts.day())),
425        DateTimeUnits::DayOfWeek => Ok(D::from(ts.day_of_week())),
426        DateTimeUnits::DayOfYear => Ok(D::from(ts.ordinal())),
427        DateTimeUnits::IsoDayOfWeek => Ok(D::from(ts.iso_day_of_week())),
428        DateTimeUnits::Hour => Ok(D::from(ts.hour())),
429        DateTimeUnits::Minute => Ok(D::from(ts.minute())),
430        DateTimeUnits::Second => Ok(ts.extract_second()),
431        DateTimeUnits::Milliseconds => Ok(ts.extract_millisecond()),
432        DateTimeUnits::Microseconds => Ok(ts.extract_microsecond()),
433        DateTimeUnits::Timezone
434        | DateTimeUnits::TimezoneHour
435        | DateTimeUnits::TimezoneMinute
436        | DateTimeUnits::IsoDayOfYear => Err(EvalError::Unsupported {
437            feature: format!("'{}' timestamp units", units).into(),
438            discussion_no: None,
439        }),
440    }
441}
442
443/// Will extracting this unit from the timestamp include the "most significant bits" of
444/// the timestamp?
445pub(crate) fn most_significant_unit(unit: DateTimeUnits) -> bool {
446    match unit {
447        DateTimeUnits::Epoch
448        | DateTimeUnits::Millennium
449        | DateTimeUnits::Century
450        | DateTimeUnits::Decade
451        | DateTimeUnits::Year => true,
452        _ => false,
453    }
454}
455
456#[derive(
457    Ord,
458    PartialOrd,
459    Clone,
460    Debug,
461    Eq,
462    PartialEq,
463    Serialize,
464    Deserialize,
465    Hash,
466    MzReflect
467)]
468pub struct ExtractTimestamp(pub DateTimeUnits);
469
470impl EagerUnaryFunc for ExtractTimestamp {
471    type Input<'a> = CheckedTimestamp<NaiveDateTime>;
472    type Output<'a> = Result<Numeric, EvalError>;
473
474    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
475        date_part_timestamp_inner(self.0, &*a)
476    }
477
478    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
479        SqlScalarType::Numeric { max_scale: None }.nullable(input.nullable)
480    }
481
482    fn is_monotone(&self) -> bool {
483        most_significant_unit(self.0)
484    }
485}
486
487impl fmt::Display for ExtractTimestamp {
488    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
489        write!(f, "extract_{}_ts", self.0)
490    }
491}
492
493#[derive(
494    Ord,
495    PartialOrd,
496    Clone,
497    Debug,
498    Eq,
499    PartialEq,
500    Serialize,
501    Deserialize,
502    Hash,
503    MzReflect
504)]
505pub struct ExtractTimestampTz(pub DateTimeUnits);
506
507impl EagerUnaryFunc for ExtractTimestampTz {
508    type Input<'a> = CheckedTimestamp<DateTime<Utc>>;
509    type Output<'a> = Result<Numeric, EvalError>;
510
511    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
512        date_part_timestamp_inner(self.0, &*a)
513    }
514
515    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
516        SqlScalarType::Numeric { max_scale: None }.nullable(input.nullable)
517    }
518
519    fn is_monotone(&self) -> bool {
520        // Unlike the timezone-less timestamp, it's not safe to extract the "high-order bits" like
521        // year: year takes timezone into account, and it's quite possible for a different timezone
522        // to be in a previous year while having a later UTC-equivalent time.
523        self.0 == DateTimeUnits::Epoch
524    }
525}
526
527impl fmt::Display for ExtractTimestampTz {
528    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
529        write!(f, "extract_{}_tstz", self.0)
530    }
531}
532
533#[derive(
534    Ord,
535    PartialOrd,
536    Clone,
537    Debug,
538    Eq,
539    PartialEq,
540    Serialize,
541    Deserialize,
542    Hash,
543    MzReflect
544)]
545pub struct DatePartTimestamp(pub DateTimeUnits);
546
547impl EagerUnaryFunc for DatePartTimestamp {
548    type Input<'a> = CheckedTimestamp<NaiveDateTime>;
549    type Output<'a> = Result<f64, EvalError>;
550
551    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
552        date_part_timestamp_inner(self.0, &*a)
553    }
554
555    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
556        SqlScalarType::Float64.nullable(input.nullable)
557    }
558}
559
560impl fmt::Display for DatePartTimestamp {
561    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
562        write!(f, "date_part_{}_ts", self.0)
563    }
564}
565
566#[derive(
567    Ord,
568    PartialOrd,
569    Clone,
570    Debug,
571    Eq,
572    PartialEq,
573    Serialize,
574    Deserialize,
575    Hash,
576    MzReflect
577)]
578pub struct DatePartTimestampTz(pub DateTimeUnits);
579
580impl EagerUnaryFunc for DatePartTimestampTz {
581    type Input<'a> = CheckedTimestamp<DateTime<Utc>>;
582    type Output<'a> = Result<f64, EvalError>;
583
584    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
585        date_part_timestamp_inner(self.0, &*a)
586    }
587
588    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
589        SqlScalarType::Float64.nullable(input.nullable)
590    }
591}
592
593impl fmt::Display for DatePartTimestampTz {
594    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
595        write!(f, "date_part_{}_tstz", self.0)
596    }
597}
598
599pub fn date_trunc_inner<T: TimestampLike>(units: DateTimeUnits, ts: &T) -> Result<T, EvalError> {
600    match units {
601        DateTimeUnits::Millennium => Ok(ts.truncate_millennium()),
602        DateTimeUnits::Century => Ok(ts.truncate_century()),
603        DateTimeUnits::Decade => Ok(ts.truncate_decade()),
604        DateTimeUnits::Year => Ok(ts.truncate_year()),
605        DateTimeUnits::Quarter => Ok(ts.truncate_quarter()),
606        DateTimeUnits::Week => Ok(ts.truncate_week()?),
607        DateTimeUnits::Day => Ok(ts.truncate_day()),
608        DateTimeUnits::Hour => Ok(ts.truncate_hour()),
609        DateTimeUnits::Minute => Ok(ts.truncate_minute()),
610        DateTimeUnits::Second => Ok(ts.truncate_second()),
611        DateTimeUnits::Month => Ok(ts.truncate_month()),
612        DateTimeUnits::Milliseconds => Ok(ts.truncate_milliseconds()),
613        DateTimeUnits::Microseconds => Ok(ts.truncate_microseconds()),
614        DateTimeUnits::Epoch
615        | DateTimeUnits::Timezone
616        | DateTimeUnits::TimezoneHour
617        | DateTimeUnits::TimezoneMinute
618        | DateTimeUnits::DayOfWeek
619        | DateTimeUnits::DayOfYear
620        | DateTimeUnits::IsoDayOfWeek
621        | DateTimeUnits::IsoDayOfYear => Err(EvalError::Unsupported {
622            feature: format!("'{}' timestamp units", units).into(),
623            discussion_no: None,
624        }),
625    }
626}
627
628#[derive(
629    Ord,
630    PartialOrd,
631    Clone,
632    Debug,
633    Eq,
634    PartialEq,
635    Serialize,
636    Deserialize,
637    Hash,
638    MzReflect
639)]
640pub struct DateTruncTimestamp(pub DateTimeUnits);
641
642impl EagerUnaryFunc for DateTruncTimestamp {
643    type Input<'a> = CheckedTimestamp<NaiveDateTime>;
644    type Output<'a> = Result<CheckedTimestamp<NaiveDateTime>, EvalError>;
645
646    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
647        date_trunc_inner(self.0, &*a)?.try_into().err_into()
648    }
649
650    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
651        SqlScalarType::Timestamp { precision: None }.nullable(input.nullable)
652    }
653
654    fn is_monotone(&self) -> bool {
655        true
656    }
657}
658
659impl fmt::Display for DateTruncTimestamp {
660    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
661        write!(f, "date_trunc_{}_ts", self.0)
662    }
663}
664
665#[derive(
666    Ord,
667    PartialOrd,
668    Clone,
669    Debug,
670    Eq,
671    PartialEq,
672    Serialize,
673    Deserialize,
674    Hash,
675    MzReflect
676)]
677pub struct DateTruncTimestampTz(pub DateTimeUnits);
678
679impl EagerUnaryFunc for DateTruncTimestampTz {
680    type Input<'a> = CheckedTimestamp<DateTime<Utc>>;
681    type Output<'a> = Result<CheckedTimestamp<DateTime<Utc>>, EvalError>;
682
683    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
684        date_trunc_inner(self.0, &*a)?.try_into().err_into()
685    }
686
687    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
688        SqlScalarType::TimestampTz { precision: None }.nullable(input.nullable)
689    }
690
691    fn is_monotone(&self) -> bool {
692        true
693    }
694}
695
696impl fmt::Display for DateTruncTimestampTz {
697    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
698        write!(f, "date_trunc_{}_tstz", self.0)
699    }
700}
701
702/// Converts the timestamp `dt`, which is assumed to be in the time of the timezone `tz` to a timestamptz in UTC.
703/// This operation is fallible because certain timestamps at timezones that observe DST are simply impossible or
704/// ambiguous. In case of ambiguity (when a hour repeats) we will prefer the latest variant, and when an hour is
705/// impossible, we will attempt to fix it by advancing it. For example, `EST` and `2020-11-11T12:39:14` would return
706/// `2020-11-11T17:39:14Z`. A DST observing timezone like `America/New_York` would cause the following DST anomalies:
707/// `2020-11-01T00:59:59` -> `2020-11-01T04:59:59Z` and `2020-11-01T01:00:00` -> `2020-11-01T06:00:00Z`
708/// `2020-03-08T02:59:59` -> `2020-03-08T07:59:59Z` and `2020-03-08T03:00:00` -> `2020-03-08T07:00:00Z`
709pub fn timezone_timestamp(
710    tz: Timezone,
711    dt: NaiveDateTime,
712) -> Result<CheckedTimestamp<DateTime<Utc>>, EvalError> {
713    let offset = match tz {
714        Timezone::FixedOffset(offset) => offset,
715        Timezone::Tz(tz) => match tz.offset_from_local_datetime(&dt).latest() {
716            Some(offset) => offset.fix(),
717            None => {
718                let dt = dt
719                    .checked_add_signed(
720                        Duration::try_hours(1).ok_or(EvalError::TimestampOutOfRange)?,
721                    )
722                    .ok_or(EvalError::TimestampOutOfRange)?;
723                tz.offset_from_local_datetime(&dt)
724                    .latest()
725                    .ok_or(EvalError::InvalidTimezoneConversion)?
726                    .fix()
727            }
728        },
729    };
730    let dt = checked_sub_with_leapsecond(&dt, &offset).ok_or(EvalError::TimestampOutOfRange)?;
731    DateTime::from_naive_utc_and_offset(dt, Utc)
732        .try_into()
733        .err_into()
734}
735
736/// Converts the UTC timestamptz `utc` to the local timestamp of the timezone `tz`.
737/// For example, `EST` and `2020-11-11T17:39:14Z` would return `2020-11-11T12:39:14`.
738pub fn timezone_timestamptz(tz: Timezone, utc: DateTime<Utc>) -> Result<NaiveDateTime, EvalError> {
739    let offset = match tz {
740        Timezone::FixedOffset(offset) => offset,
741        Timezone::Tz(tz) => tz.offset_from_utc_datetime(&utc.naive_utc()).fix(),
742    };
743    checked_add_with_leapsecond(&utc.naive_utc(), &offset).ok_or(EvalError::TimestampOutOfRange)
744}
745
746/// Checked addition that is missing from chrono. Adapt its methods here but add a check.
747fn checked_add_with_leapsecond(lhs: &NaiveDateTime, rhs: &FixedOffset) -> Option<NaiveDateTime> {
748    // extract and temporarily remove the fractional part and later recover it
749    let nanos = lhs.nanosecond();
750    let lhs = lhs.with_nanosecond(0).unwrap();
751    let rhs = rhs.local_minus_utc();
752    let dt = lhs.checked_add_signed(chrono::Duration::try_seconds(i64::from(rhs))?)?;
753    // chrono represents a leap second as `nanos >= 1_000_000_000`, but only on a
754    // second-of-minute of 59. If the offset shifted us off `:59`, we can't keep
755    // the leap-second representation: the resulting `NaiveTime` would be
756    // unconstructable via `from_num_seconds_from_midnight_opt` and would panic
757    // when round-tripped through `Row` encoding. In that case, fold the leap
758    // second into the next regular second.
759    if nanos >= 1_000_000_000 && dt.second() != 59 {
760        dt.checked_add_signed(chrono::Duration::nanoseconds(i64::from(nanos)))
761    } else {
762        Some(dt.with_nanosecond(nanos).unwrap())
763    }
764}
765
766/// Checked subtraction that is missing from chrono. Adapt its methods here but add a check.
767fn checked_sub_with_leapsecond(lhs: &NaiveDateTime, rhs: &FixedOffset) -> Option<NaiveDateTime> {
768    // extract and temporarily remove the fractional part and later recover it
769    let nanos = lhs.nanosecond();
770    let lhs = lhs.with_nanosecond(0).unwrap();
771    let rhs = rhs.local_minus_utc();
772    let dt = lhs.checked_sub_signed(chrono::Duration::try_seconds(i64::from(rhs))?)?;
773    // See `checked_add_with_leapsecond` for why we have to special-case
774    // leap-second nanos that no longer land on `:59` after applying the offset.
775    if nanos >= 1_000_000_000 && dt.second() != 59 {
776        dt.checked_add_signed(chrono::Duration::nanoseconds(i64::from(nanos)))
777    } else {
778        Some(dt.with_nanosecond(nanos).unwrap())
779    }
780}
781
782#[derive(
783    Ord,
784    PartialOrd,
785    Clone,
786    Debug,
787    Eq,
788    PartialEq,
789    Serialize,
790    Deserialize,
791    Hash,
792    MzReflect
793)]
794pub struct TimezoneTimestamp(pub Timezone);
795
796impl EagerUnaryFunc for TimezoneTimestamp {
797    type Input<'a> = CheckedTimestamp<NaiveDateTime>;
798    type Output<'a> = Result<CheckedTimestamp<DateTime<Utc>>, EvalError>;
799
800    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
801        timezone_timestamp(self.0, a.to_naive())
802    }
803
804    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
805        SqlScalarType::TimestampTz { precision: None }.nullable(input.nullable)
806    }
807}
808
809impl fmt::Display for TimezoneTimestamp {
810    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
811        write!(f, "timezone_{}_ts", self.0)
812    }
813}
814
815#[derive(
816    Ord,
817    PartialOrd,
818    Clone,
819    Debug,
820    Eq,
821    PartialEq,
822    Serialize,
823    Deserialize,
824    Hash,
825    MzReflect
826)]
827pub struct TimezoneTimestampTz(pub Timezone);
828
829impl EagerUnaryFunc for TimezoneTimestampTz {
830    type Input<'a> = CheckedTimestamp<DateTime<Utc>>;
831    type Output<'a> = Result<CheckedTimestamp<NaiveDateTime>, EvalError>;
832
833    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
834        timezone_timestamptz(self.0, a.into())?
835            .try_into()
836            .err_into()
837    }
838
839    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
840        SqlScalarType::Timestamp { precision: None }.nullable(input.nullable)
841    }
842}
843
844impl fmt::Display for TimezoneTimestampTz {
845    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
846        write!(f, "timezone_{}_tstz", self.0)
847    }
848}
849
850#[derive(
851    Clone,
852    Debug,
853    PartialEq,
854    Eq,
855    PartialOrd,
856    Ord,
857    Hash,
858    Serialize,
859    Deserialize,
860    MzReflect
861)]
862pub struct ToCharTimestamp {
863    pub format_string: String,
864    pub format: DateTimeFormat,
865}
866
867impl EagerUnaryFunc for ToCharTimestamp {
868    type Input<'a> = CheckedTimestamp<NaiveDateTime>;
869    type Output<'a> = String;
870
871    fn call<'a>(&self, input: Self::Input<'a>) -> Self::Output<'a> {
872        self.format.render(&*input)
873    }
874
875    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
876        SqlScalarType::String.nullable(input.nullable)
877    }
878}
879
880impl fmt::Display for ToCharTimestamp {
881    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
882        write!(f, "tocharts[{}]", self.format_string)
883    }
884}
885
886#[derive(
887    Clone,
888    Debug,
889    PartialEq,
890    Eq,
891    PartialOrd,
892    Ord,
893    Hash,
894    Serialize,
895    Deserialize,
896    MzReflect
897)]
898pub struct ToCharTimestampTz {
899    pub format_string: String,
900    pub format: DateTimeFormat,
901}
902
903impl EagerUnaryFunc for ToCharTimestampTz {
904    type Input<'a> = CheckedTimestamp<DateTime<Utc>>;
905    type Output<'a> = String;
906
907    fn call<'a>(&self, input: Self::Input<'a>) -> Self::Output<'a> {
908        self.format.render(&*input)
909    }
910
911    fn output_sql_type(&self, input: SqlColumnType) -> SqlColumnType {
912        SqlScalarType::String.nullable(input.nullable)
913    }
914}
915
916impl fmt::Display for ToCharTimestampTz {
917    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
918        write!(f, "tochartstz[{}]", self.format_string)
919    }
920}
921
922#[sqlfunc(sqlname = "timezonets")]
923fn timezone_timestamp_binary(
924    tz: &str,
925    ts: CheckedTimestamp<NaiveDateTime>,
926) -> Result<CheckedTimestamp<DateTime<Utc>>, EvalError> {
927    let tz = parse_timezone(tz, TimezoneSpec::Posix)?;
928    timezone_timestamp(tz, ts.into())
929}
930
931#[sqlfunc(sqlname = "timezonetstz")]
932fn timezone_timestamp_tz_binary(
933    tz: &str,
934    tstz: CheckedTimestamp<DateTime<Utc>>,
935) -> Result<CheckedTimestamp<NaiveDateTime>, EvalError> {
936    let tz = parse_timezone(tz, TimezoneSpec::Posix)?;
937    Ok(timezone_timestamptz(tz, tstz.into())?.try_into()?)
938}
mz_expr/scalar/func/impls/timestamp.rs

mz_expr/scalar/func/impls/
timestamp.rs
