sqlmesh.utils.date

  1from __future__ import annotations
  2
  3import re
  4import time
  5import typing as t
  6import warnings
  7
  8from pandas.api.types import is_datetime64_any_dtype  # type: ignore
  9
 10from datetime import date, datetime, timedelta, timezone
 11
 12import dateparser
 13import pandas as pd
 14from dateparser import freshness_date_parser as freshness_date_parser_module
 15from dateparser.freshness_date_parser import freshness_date_parser
 16from sqlglot import exp
 17
 18from sqlmesh.utils import ttl_cache
 19
 20UTC = timezone.utc
 21TimeLike = t.Union[date, datetime, str, int, float]
 22DATE_INT_FMT = "%Y%m%d"
 23
 24if t.TYPE_CHECKING:
 25    from sqlmesh.core.scheduler import Interval
 26
 27warnings.filterwarnings(
 28    "ignore",
 29    message="The localize method is no longer necessary, as this time zone supports the fold attribute",
 30)
 31
 32
 33# The Freshness Date Data Parser doesn't support plural units so we add the `s?` to the expression
 34freshness_date_parser_module.PATTERN = re.compile(
 35    r"(\d+[.,]?\d*)\s*(%s)s?\b" % freshness_date_parser_module._UNITS,  # type: ignore
 36    re.I | re.S | re.U,  # type: ignore
 37)
 38DAY_SHORTCUT_EXPRESSIONS = {"today", "yesterday", "tomorrow"}
 39TIME_UNITS = {"hours", "minutes", "seconds"}
 40TEMPORAL_TZ_TYPES = {
 41    exp.DataType.Type.TIMETZ,
 42    exp.DataType.Type.TIMESTAMPTZ,
 43    exp.DataType.Type.TIMESTAMPLTZ,
 44}
 45
 46
 47def now(minute_floor: bool = True) -> datetime:
 48    """
 49    Current utc datetime with optional minute level accuracy / granularity.
 50
 51    minute_floor is set to True by default
 52
 53    Args:
 54        minute_floor: If true (default), removes the second and microseconds from the current datetime.
 55
 56    Returns:
 57        A datetime object with tz utc.
 58    """
 59    now = datetime.now(tz=UTC)
 60    if minute_floor:
 61        return now.replace(second=0, microsecond=0, tzinfo=UTC)
 62    return now.replace(tzinfo=UTC)
 63
 64
 65def now_timestamp(minute_floor: bool = False) -> int:
 66    """
 67    Current utc timestamp.
 68
 69    Args:
 70        minute_floor: If true, removes the second and microseconds from the current datetime.
 71
 72    Returns:
 73        UTC epoch millis timestamp
 74    """
 75    return to_timestamp(now(minute_floor))
 76
 77
 78def now_ds() -> str:
 79    """
 80    Current utc ds.
 81
 82    Returns:
 83        Today's ds string.
 84    """
 85    return to_ds(now())
 86
 87
 88def yesterday() -> datetime:
 89    """
 90    Yesterday utc datetime.
 91
 92    Returns:
 93        A datetime object with tz utc representing yesterday's date
 94    """
 95    return to_datetime("yesterday")
 96
 97
 98def yesterday_ds() -> str:
 99    """
100    Yesterday utc ds.
101
102    Returns:
103        Yesterday's ds string.
104    """
105    return to_ds("yesterday")
106
107
108def yesterday_timestamp() -> int:
109    """
110    Yesterday utc timestamp.
111
112    Returns:
113        UTC epoch millis timestamp of yesterday
114    """
115    return to_timestamp(yesterday())
116
117
118def to_timestamp(value: TimeLike, relative_base: t.Optional[datetime] = None) -> int:
119    """
120    Converts a value into an epoch millis timestamp.
121
122    Args:
123        value: A variety of date formats. If value is a string, it must be in iso format.
124        relative_base: The datetime to reference for time expressions that are using relative terms
125
126    Returns:
127        Epoch millis timestamp.
128    """
129    return int(to_datetime(value, relative_base=relative_base).timestamp() * 1000)
130
131
132@ttl_cache()
133def to_datetime(value: TimeLike, relative_base: t.Optional[datetime] = None) -> datetime:
134    """Converts a value into a UTC datetime object.
135
136    Args:
137        value: A variety of date formats. If the value is number-like, it is assumed to be millisecond epochs.
138        relative_base: The datetime to reference for time expressions that are using relative terms
139
140    Raises:
141        ValueError if value cannot be converted to a datetime.
142
143    Returns:
144        A datetime object with tz utc.
145    """
146    if isinstance(value, datetime):
147        dt: t.Optional[datetime] = value
148    elif isinstance(value, date):
149        dt = datetime(value.year, value.month, value.day)
150    elif isinstance(value, exp.Expression):
151        return to_datetime(value.name)
152    else:
153        try:
154            epoch = float(value)
155        except ValueError:
156            epoch = None
157
158        if epoch is None:
159            relative_base = relative_base or now()
160            expression = str(value)
161            if is_catagorical_relative_expression(expression):
162                relative_base = relative_base.replace(hour=0, minute=0, second=0, microsecond=0)
163            dt = dateparser.parse(expression, settings={"RELATIVE_BASE": relative_base})
164        else:
165            try:
166                dt = datetime.strptime(str(value), DATE_INT_FMT)
167            except ValueError:
168                dt = datetime.fromtimestamp(epoch / 1000.0, tz=UTC)
169
170    if dt is None:
171        raise ValueError(f"Could not convert `{value}` to datetime.")
172
173    if dt.tzinfo:
174        return dt if dt.tzinfo == UTC else dt.astimezone(UTC)
175    return dt.replace(tzinfo=UTC)
176
177
178def to_date(value: TimeLike, relative_base: t.Optional[datetime] = None) -> date:
179    """Converts a value into a UTC date object
180    Args:
181        value: A variety of date formats. If the value is number-like, it is assumed to be millisecond epochs.
182        relative_base: The datetime to reference for time expressions that are using relative terms
183
184    Raises:
185        ValueError if value cannot be converted to a date.
186
187    Returns:
188        A date object with tz utc.
189    """
190    return to_datetime(value, relative_base).date()
191
192
193def date_dict(
194    execution_time: TimeLike, start: t.Optional[TimeLike], end: t.Optional[TimeLike]
195) -> t.Dict[str, t.Union[str, datetime, date, float, int]]:
196    """Creates a kwarg dictionary of datetime variables for use in SQL Contexts.
197
198    Keys are like start_date, start_ds, end_date, end_ds...
199
200    Args:
201        execution_time: Execution time.
202        start: Start time.
203        end: End time.
204
205    Returns:
206        A dictionary with various keys pointing to datetime formats.
207    """
208    kwargs: t.Dict[str, t.Union[str, datetime, date, float, int]] = {}
209
210    execution_dt = to_datetime(execution_time)
211    prefixes = [
212        ("latest", execution_dt),  # TODO: Preserved for backward compatibility. Remove in 1.0.0.
213        ("execution", execution_dt),
214    ]
215
216    if start is not None:
217        prefixes.append(("start", to_datetime(start)))
218    if end is not None:
219        prefixes.append(("end", to_datetime(end)))
220
221    for prefix, time_like in prefixes:
222        dt = to_datetime(time_like)
223        millis = to_timestamp(time_like)
224        kwargs[f"{prefix}_dt"] = dt
225        kwargs[f"{prefix}_date"] = to_date(dt)
226        kwargs[f"{prefix}_ds"] = to_ds(time_like)
227        kwargs[f"{prefix}_ts"] = to_ts(dt)
228        kwargs[f"{prefix}_tstz"] = to_tstz(dt)
229        kwargs[f"{prefix}_epoch"] = millis / 1000
230        kwargs[f"{prefix}_millis"] = millis
231        kwargs[f"{prefix}_hour"] = dt.hour
232    return kwargs
233
234
235def to_ds(obj: TimeLike) -> str:
236    """Converts a TimeLike object into YYYY-MM-DD formatted string."""
237    return to_ts(obj)[0:10]
238
239
240def to_ts(obj: TimeLike) -> str:
241    """Converts a TimeLike object into YYYY-MM-DD HH:MM:SS formatted string."""
242    return to_datetime(obj).replace(tzinfo=None).isoformat(sep=" ")
243
244
245def to_tstz(obj: TimeLike) -> str:
246    """Converts a TimeLike object into YYYY-MM-DD HH:MM:SS+00:00 formatted string."""
247    return to_datetime(obj).isoformat(sep=" ")
248
249
250def is_date(obj: TimeLike) -> bool:
251    """Checks if a TimeLike object should be treated like a date."""
252    if isinstance(obj, date) and not isinstance(obj, datetime):
253        return True
254
255    try:
256        time.strptime(str(obj).replace("-", ""), DATE_INT_FMT)
257        return True
258    except ValueError:
259        return False
260
261
262def make_inclusive(start: TimeLike, end: TimeLike) -> Interval:
263    """Adjust start and end times to to become inclusive datetimes.
264
265    SQLMesh treats start and end times as inclusive so that filters can be written as
266
267    SELECT * FROM x WHERE ds BETWEEN @start_ds AND @end_ds.
268    SELECT * FROM x WHERE ts BETWEEN @start_ts AND @end_ts.
269
270    In the ds ('2020-01-01') case, because start_ds and end_ds are categorical, between works even if
271    start_ds and end_ds are equivalent. However, when we move to ts ('2022-01-01 12:00:00'), because timestamps
272    are numeric, using simple equality doesn't make sense. When the end is not a categorical date, then it is
273    treated as an exclusive range and converted to inclusive by subtracting 1 millisecond.
274
275    Args:
276        start: Start timelike object.
277        end: End timelike object.
278
279    Example:
280        >>> make_inclusive("2020-01-01", "2020-01-01")
281        (datetime.datetime(2020, 1, 1, 0, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 1, 1, 23, 59, 59, 999999, tzinfo=datetime.timezone.utc))
282
283    Returns:
284        A tuple of inclusive datetime objects.
285    """
286    return (to_datetime(start), make_inclusive_end(end))
287
288
289def make_inclusive_end(end: TimeLike) -> datetime:
290    end_dt = to_datetime(end)
291    if is_date(end):
292        end_dt = end_dt + timedelta(days=1)
293    return end_dt - timedelta(microseconds=1)
294
295
296def validate_date_range(
297    start: t.Optional[TimeLike],
298    end: t.Optional[TimeLike],
299) -> None:
300    if start and end and to_datetime(start) > to_datetime(end):
301        raise ValueError(
302            f"Start date / time ({start}) can't be greater than end date / time ({end})"
303        )
304
305
306def time_like_to_str(time_like: TimeLike) -> str:
307    if isinstance(time_like, str):
308        return time_like
309    if is_date(time_like):
310        return to_ds(time_like)
311    return to_ts(time_like)
312
313
314def is_catagorical_relative_expression(expression: str) -> bool:
315    if expression.strip().lower() in DAY_SHORTCUT_EXPRESSIONS:
316        return True
317    grain_kwargs = freshness_date_parser.get_kwargs(expression)
318    if not grain_kwargs:
319        return False
320    return not any(k in TIME_UNITS for k in grain_kwargs)
321
322
323def to_time_column(
324    time_column: t.Union[TimeLike, exp.Null],
325    time_column_type: exp.DataType,
326    time_column_format: t.Optional[str] = None,
327) -> exp.Expression:
328    """Convert a TimeLike object to the same time format and type as the model's time column."""
329    if isinstance(time_column, exp.Null):
330        return exp.cast(time_column, to=time_column_type)
331    if time_column_type.is_type(exp.DataType.Type.DATE):
332        return exp.cast(exp.Literal.string(to_ds(time_column)), to="date")
333    if time_column_type.this in TEMPORAL_TZ_TYPES:
334        return exp.cast(exp.Literal.string(to_tstz(time_column)), to=time_column_type.this)
335    if time_column_type.this in exp.DataType.TEMPORAL_TYPES:
336        return exp.cast(exp.Literal.string(to_ts(time_column)), to=time_column_type.this)
337
338    if time_column_format:
339        time_column = to_datetime(time_column).strftime(time_column_format)
340    if time_column_type.this in exp.DataType.TEXT_TYPES:
341        return exp.Literal.string(time_column)
342    if time_column_type.this in exp.DataType.NUMERIC_TYPES:
343        return exp.Literal.number(time_column)
344    return exp.convert(time_column)
345
346
347def pandas_timestamp_to_pydatetime(
348    df: pd.DataFrame, columns_to_types: t.Optional[t.Dict[str, exp.DataType]]
349) -> pd.DataFrame:
350    for column in df.columns:
351        if is_datetime64_any_dtype(df.dtypes[column]):
352            # We must use `pd.Series` and dtype or pandas will convert it back to pd.Timestamp during assignment
353            # https://stackoverflow.com/a/68961834/1707525
354            df[column] = pd.Series(df[column].dt.to_pydatetime(), dtype="object")
355
356            if columns_to_types and columns_to_types[column].this in (
357                exp.DataType.Type.DATE,
358                exp.DataType.Type.DATE32,
359            ):
360                # Sometimes `to_pydatetime()` has already converted to date, so we only extract from datetime objects.
361                df[column] = df[column].map(
362                    lambda x: x.date() if type(x) is datetime and not pd.isna(x) else x
363                )
364
365    return df