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_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_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_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_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_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_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_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_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_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_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_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_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    DateTime::from_naive_utc_and_offset(dt - offset, Utc)
731        .try_into()
732        .err_into()
733}
734
735/// Converts the UTC timestamptz `utc` to the local timestamp of the timezone `tz`.
736/// For example, `EST` and `2020-11-11T17:39:14Z` would return `2020-11-11T12:39:14`.
737pub fn timezone_timestamptz(tz: Timezone, utc: DateTime<Utc>) -> Result<NaiveDateTime, EvalError> {
738    let offset = match tz {
739        Timezone::FixedOffset(offset) => offset,
740        Timezone::Tz(tz) => tz.offset_from_utc_datetime(&utc.naive_utc()).fix(),
741    };
742    checked_add_with_leapsecond(&utc.naive_utc(), &offset).ok_or(EvalError::TimestampOutOfRange)
743}
744
745/// Checked addition that is missing from chrono. Adapt its methods here but add a check.
746fn checked_add_with_leapsecond(lhs: &NaiveDateTime, rhs: &FixedOffset) -> Option<NaiveDateTime> {
747    // extract and temporarily remove the fractional part and later recover it
748    let nanos = lhs.nanosecond();
749    let lhs = lhs.with_nanosecond(0).unwrap();
750    let rhs = rhs.local_minus_utc();
751    lhs.checked_add_signed(match chrono::Duration::try_seconds(i64::from(rhs)) {
752        Some(dur) => dur,
753        None => return None,
754    })
755    .map(|dt| dt.with_nanosecond(nanos).unwrap())
756}
757
758#[derive(
759    Ord,
760    PartialOrd,
761    Clone,
762    Debug,
763    Eq,
764    PartialEq,
765    Serialize,
766    Deserialize,
767    Hash,
768    MzReflect
769)]
770pub struct TimezoneTimestamp(pub Timezone);
771
772impl EagerUnaryFunc for TimezoneTimestamp {
773    type Input<'a> = CheckedTimestamp<NaiveDateTime>;
774    type Output<'a> = Result<CheckedTimestamp<DateTime<Utc>>, EvalError>;
775
776    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
777        timezone_timestamp(self.0, a.to_naive())
778    }
779
780    fn output_type(&self, input: SqlColumnType) -> SqlColumnType {
781        SqlScalarType::TimestampTz { precision: None }.nullable(input.nullable)
782    }
783}
784
785impl fmt::Display for TimezoneTimestamp {
786    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
787        write!(f, "timezone_{}_ts", self.0)
788    }
789}
790
791#[derive(
792    Ord,
793    PartialOrd,
794    Clone,
795    Debug,
796    Eq,
797    PartialEq,
798    Serialize,
799    Deserialize,
800    Hash,
801    MzReflect
802)]
803pub struct TimezoneTimestampTz(pub Timezone);
804
805impl EagerUnaryFunc for TimezoneTimestampTz {
806    type Input<'a> = CheckedTimestamp<DateTime<Utc>>;
807    type Output<'a> = Result<CheckedTimestamp<NaiveDateTime>, EvalError>;
808
809    fn call<'a>(&self, a: Self::Input<'a>) -> Self::Output<'a> {
810        timezone_timestamptz(self.0, a.into())?
811            .try_into()
812            .err_into()
813    }
814
815    fn output_type(&self, input: SqlColumnType) -> SqlColumnType {
816        SqlScalarType::Timestamp { precision: None }.nullable(input.nullable)
817    }
818}
819
820impl fmt::Display for TimezoneTimestampTz {
821    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
822        write!(f, "timezone_{}_tstz", self.0)
823    }
824}
825
826#[derive(
827    Clone,
828    Debug,
829    PartialEq,
830    Eq,
831    PartialOrd,
832    Ord,
833    Hash,
834    Serialize,
835    Deserialize,
836    MzReflect
837)]
838pub struct ToCharTimestamp {
839    pub format_string: String,
840    pub format: DateTimeFormat,
841}
842
843impl EagerUnaryFunc for ToCharTimestamp {
844    type Input<'a> = CheckedTimestamp<NaiveDateTime>;
845    type Output<'a> = String;
846
847    fn call<'a>(&self, input: Self::Input<'a>) -> Self::Output<'a> {
848        self.format.render(&*input)
849    }
850
851    fn output_type(&self, input: SqlColumnType) -> SqlColumnType {
852        SqlScalarType::String.nullable(input.nullable)
853    }
854}
855
856impl fmt::Display for ToCharTimestamp {
857    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
858        write!(f, "tocharts[{}]", self.format_string)
859    }
860}
861
862#[derive(
863    Clone,
864    Debug,
865    PartialEq,
866    Eq,
867    PartialOrd,
868    Ord,
869    Hash,
870    Serialize,
871    Deserialize,
872    MzReflect
873)]
874pub struct ToCharTimestampTz {
875    pub format_string: String,
876    pub format: DateTimeFormat,
877}
878
879impl EagerUnaryFunc for ToCharTimestampTz {
880    type Input<'a> = CheckedTimestamp<DateTime<Utc>>;
881    type Output<'a> = String;
882
883    fn call<'a>(&self, input: Self::Input<'a>) -> Self::Output<'a> {
884        self.format.render(&*input)
885    }
886
887    fn output_type(&self, input: SqlColumnType) -> SqlColumnType {
888        SqlScalarType::String.nullable(input.nullable)
889    }
890}
891
892impl fmt::Display for ToCharTimestampTz {
893    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
894        write!(f, "tochartstz[{}]", self.format_string)
895    }
896}
897
898#[sqlfunc(sqlname = "timezonets")]
899fn timezone_timestamp_binary(
900    tz: &str,
901    ts: CheckedTimestamp<NaiveDateTime>,
902) -> Result<CheckedTimestamp<DateTime<Utc>>, EvalError> {
903    let tz = parse_timezone(tz, TimezoneSpec::Posix)?;
904    timezone_timestamp(tz, ts.into())
905}
906
907#[sqlfunc(sqlname = "timezonetstz")]
908fn timezone_timestamp_tz_binary(
909    tz: &str,
910    tstz: CheckedTimestamp<DateTime<Utc>>,
911) -> Result<CheckedTimestamp<NaiveDateTime>, EvalError> {
912    let tz = parse_timezone(tz, TimezoneSpec::Posix)?;
913    Ok(timezone_timestamptz(tz, tstz.into())?.try_into()?)
914}
mz_expr/scalar/func/impls/timestamp.rs

mz_expr/scalar/func/impls/
timestamp.rs
