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

mz_expr/scalar/func/impls/
timestamp.rs
