1"""sqlglot expressions - date, time, and timestamp functions."""
  2
  3from __future__ import annotations
  4
  5import typing as t
  6
  7from sqlglot.expressions.core import (
  8    Expression,
  9    Func,
 10    TimeUnit,
 11    IntervalOp,
 12    Literal,
 13    Column,
 14    TIMESTAMP_PARTS,
 15)
 16from sqlglot.expressions.datatypes import DataType, DType
 17
 18if t.TYPE_CHECKING:
 19    from sqlglot.expressions.core import Expr, Neg
 20
 21
 22# Current date/time
 23
 24
 25class CurrentDate(Expression, Func):
 26    arg_types = {"this": False}
 27
 28
 29class CurrentDatetime(Expression, Func):
 30    arg_types = {"this": False}
 31
 32
 33class CurrentTime(Expression, Func):
 34    arg_types = {"this": False}
 35
 36
 37class CurrentTimestamp(Expression, Func):
 38    arg_types = {"this": False, "sysdate": False}
 39
 40
 41class CurrentTimestampLTZ(Expression, Func):
 42    arg_types = {}
 43
 44
 45class CurrentTimezone(Expression, Func):
 46    arg_types = {}
 47
 48
 49class Localtime(Expression, Func):
 50    arg_types = {"this": False}
 51
 52
 53class Localtimestamp(Expression, Func):
 54    arg_types = {"this": False}
 55
 56
 57class Systimestamp(Expression, Func):
 58    arg_types = {"this": False}
 59
 60
 61class UtcDate(Expression, Func):
 62    arg_types = {}
 63
 64
 65class UtcTime(Expression, Func):
 66    arg_types = {"this": False}
 67
 68
 69class UtcTimestamp(Expression, Func):
 70    arg_types = {"this": False}
 71
 72
 73# Date arithmetic
 74
 75
 76class AddMonths(Expression, Func):
 77    arg_types = {"this": True, "expression": True, "preserve_end_of_month": False}
 78
 79
 80class DateAdd(Expression, Func, IntervalOp):
 81    arg_types = {"this": True, "expression": True, "unit": False}
 82
 83
 84class DateBin(Expression, Func, IntervalOp):
 85    arg_types = {"this": True, "expression": True, "unit": False, "zone": False, "origin": False}
 86
 87
 88class DateDiff(Expression, Func, TimeUnit):
 89    _sql_names = ["DATEDIFF", "DATE_DIFF"]
 90    arg_types = {
 91        "this": True,
 92        "expression": True,
 93        "unit": False,
 94        "zone": False,
 95        "big_int": False,
 96        "date_part_boundary": False,
 97    }
 98
 99
100class DateSub(Expression, Func, IntervalOp):
101    arg_types = {"this": True, "expression": True, "unit": False}
102
103
104class DatetimeAdd(Expression, Func, IntervalOp):
105    arg_types = {"this": True, "expression": True, "unit": False}
106
107
108class DatetimeDiff(Expression, Func, TimeUnit):
109    arg_types = {"this": True, "expression": True, "unit": False}
110
111
112class DatetimeSub(Expression, Func, IntervalOp):
113    arg_types = {"this": True, "expression": True, "unit": False}
114
115
116class MonthsBetween(Expression, Func):
117    arg_types = {"this": True, "expression": True, "roundoff": False}
118
119
120class TimeAdd(Expression, Func, TimeUnit):
121    arg_types = {"this": True, "expression": True, "unit": False}
122
123
124class TimeDiff(Expression, Func, TimeUnit):
125    arg_types = {"this": True, "expression": True, "unit": False}
126
127
128class TimeSub(Expression, Func, TimeUnit):
129    arg_types = {"this": True, "expression": True, "unit": False}
130
131
132class TimestampAdd(Expression, Func, TimeUnit):
133    arg_types = {"this": True, "expression": True, "unit": False}
134
135
136class TimestampDiff(Expression, Func, TimeUnit):
137    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
138    arg_types = {"this": True, "expression": True, "unit": False}
139
140
141class TimestampSub(Expression, Func, TimeUnit):
142    arg_types = {"this": True, "expression": True, "unit": False}
143
144
145class TsOrDsAdd(Expression, Func, TimeUnit):
146    # return_type is used to correctly cast the arguments of this expression when transpiling it
147    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
148
149    @property
150    def return_type(self) -> DataType:
151        return DataType.build(self.args.get("return_type") or DType.DATE)
152
153
154class TsOrDsDiff(Expression, Func, TimeUnit):
155    arg_types = {"this": True, "expression": True, "unit": False}
156
157
158# Truncation
159
160
161class DatetimeTrunc(Expression, Func, TimeUnit):
162    arg_types = {"this": True, "unit": True, "zone": False}
163
164
165class DateTrunc(Expression, Func):
166    arg_types = {"unit": True, "this": True, "zone": False, "input_type_preserved": False}
167
168    def __init__(self, **args):
169        # Across most dialects it's safe to unabbreviate the unit (e.g. 'Q' -> 'QUARTER') except Oracle
170        # https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROUND-and-TRUNC-Date-Functions.html
171        unabbreviate = args.pop("unabbreviate", True)
172
173        unit = args.get("unit")
174        if isinstance(unit, TimeUnit.VAR_LIKE) and not (
175            isinstance(unit, Column) and len(unit.parts) != 1
176        ):
177            unit_name = unit.name.upper()
178            if unabbreviate and unit_name in TimeUnit.UNABBREVIATED_UNIT_NAME:
179                unit_name = TimeUnit.UNABBREVIATED_UNIT_NAME[unit_name]
180
181            args["unit"] = Literal.string(unit_name)
182
183        super().__init__(**args)
184
185    @property
186    def unit(self) -> Expr:
187        return self.args["unit"]
188
189
190class TimestampTrunc(Expression, Func, TimeUnit):
191    arg_types = {"this": True, "unit": True, "zone": False, "input_type_preserved": False}
192
193
194class TimeSlice(Expression, Func, TimeUnit):
195    arg_types = {"this": True, "expression": True, "unit": True, "kind": False}
196
197
198class TimeTrunc(Expression, Func, TimeUnit):
199    arg_types = {"this": True, "unit": True, "zone": False}
200
201
202# Date/time extraction
203
204
205class Day(Expression, Func):
206    pass
207
208
209class DayOfMonth(Expression, Func):
210    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
211
212
213class DayOfWeek(Expression, Func):
214    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
215
216
217class DayOfWeekIso(Expression, Func):
218    _sql_names = ["DAYOFWEEK_ISO", "ISODOW"]
219
220
221class DayOfYear(Expression, Func):
222    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
223
224
225class Dayname(Expression, Func):
226    arg_types = {"this": True, "abbreviated": False}
227
228
229class Extract(Expression, Func):
230    arg_types = {"this": True, "expression": True}
231
232
233class GetExtract(Expression, Func):
234    arg_types = {"this": True, "expression": True}
235
236
237class Hour(Expression, Func):
238    pass
239
240
241class Minute(Expression, Func):
242    pass
243
244
245class Month(Expression, Func):
246    pass
247
248
249class Monthname(Expression, Func):
250    arg_types = {"this": True, "abbreviated": False}
251
252
253class Quarter(Expression, Func):
254    pass
255
256
257class Second(Expression, Func):
258    pass
259
260
261class ToDays(Expression, Func):
262    pass
263
264
265class Week(Expression, Func):
266    arg_types = {"this": True, "mode": False}
267
268
269class WeekOfYear(Expression, Func):
270    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
271
272
273class Year(Expression, Func):
274    pass
275
276
277class YearOfWeek(Expression, Func):
278    _sql_names = ["YEAR_OF_WEEK", "YEAROFWEEK"]
279
280
281class YearOfWeekIso(Expression, Func):
282    _sql_names = ["YEAR_OF_WEEK_ISO", "YEAROFWEEKISO"]
283
284
285# Date/time construction
286
287
288class Date(Expression, Func):
289    arg_types = {"this": False, "zone": False, "expressions": False}
290    is_var_len_args = True
291
292
293class DateFromParts(Expression, Func):
294    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
295    arg_types = {"year": True, "month": False, "day": False, "allow_overflow": False}
296
297
298class DateFromUnixDate(Expression, Func):
299    pass
300
301
302class Datetime(Expression, Func):
303    arg_types = {"this": True, "expression": False}
304
305
306class GapFill(Expression, Func):
307    arg_types = {
308        "this": True,
309        "ts_column": True,
310        "bucket_width": True,
311        "partitioning_columns": False,
312        "value_columns": False,
313        "origin": False,
314        "ignore_nulls": False,
315    }
316
317
318class GenerateDateArray(Expression, Func):
319    arg_types = {"start": True, "end": True, "step": False}
320
321
322class GenerateTimestampArray(Expression, Func):
323    arg_types = {"start": True, "end": True, "step": True}
324
325
326class JustifyDays(Expression, Func):
327    pass
328
329
330class JustifyHours(Expression, Func):
331    pass
332
333
334class JustifyInterval(Expression, Func):
335    pass
336
337
338class LastDay(Expression, Func, TimeUnit):
339    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
340    arg_types = {"this": True, "unit": False}
341
342
343class MakeInterval(Expression, Func):
344    arg_types = {
345        "year": False,
346        "month": False,
347        "week": False,
348        "day": False,
349        "hour": False,
350        "minute": False,
351        "second": False,
352    }
353
354
355class NextDay(Expression, Func):
356    arg_types = {"this": True, "expression": True}
357
358
359class PreviousDay(Expression, Func):
360    arg_types = {"this": True, "expression": True}
361
362
363class Time(Expression, Func):
364    arg_types = {"this": False, "zone": False}
365
366
367class TimeFromParts(Expression, Func):
368    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
369    arg_types = {
370        "hour": True,
371        "min": True,
372        "sec": True,
373        "nano": False,
374        "fractions": False,
375        "precision": False,
376        "overflow": False,
377    }
378
379
380class Timestamp(Expression, Func):
381    arg_types = {"this": False, "zone": False, "with_tz": False}
382
383
384class TimestampFromParts(Expression, Func):
385    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
386    arg_types = {
387        **TIMESTAMP_PARTS,
388        "zone": False,
389        "milli": False,
390        "this": False,
391        "expression": False,
392    }
393
394
395class TimestampLtzFromParts(Expression, Func):
396    _sql_names = ["TIMESTAMP_LTZ_FROM_PARTS", "TIMESTAMPLTZFROMPARTS"]
397    arg_types = TIMESTAMP_PARTS.copy()
398
399
400class TimestampTzFromParts(Expression, Func):
401    _sql_names = ["TIMESTAMP_TZ_FROM_PARTS", "TIMESTAMPTZFROMPARTS"]
402    arg_types = {
403        **TIMESTAMP_PARTS,
404        "zone": False,
405    }
406
407
408# Date/time conversion
409
410
411class ConvertTimezone(Expression, Func):
412    arg_types = {
413        "source_tz": False,
414        "target_tz": True,
415        "timestamp": True,
416        "options": False,
417    }
418
419
420class DateStrToDate(Expression, Func):
421    pass
422
423
424class DateToDateStr(Expression, Func):
425    pass
426
427
428class DateToDi(Expression, Func):
429    pass
430
431
432class DiToDate(Expression, Func):
433    pass
434
435
436class FromISO8601Timestamp(Expression, Func):
437    _sql_names = ["FROM_ISO8601_TIMESTAMP"]
438
439
440class ParseDatetime(Expression, Func):
441    arg_types = {"this": True, "format": False, "zone": False}
442
443
444class ParseTime(Expression, Func):
445    arg_types = {"this": True, "format": True}
446
447
448class StrToDate(Expression, Func):
449    arg_types = {"this": True, "format": False, "safe": False}
450
451
452class StrToTime(Expression, Func):
453    arg_types = {"this": True, "format": True, "zone": False, "safe": False, "target_type": False}
454
455
456class StrToUnix(Expression, Func):
457    arg_types = {"this": False, "format": False}
458
459
460class TimeStrToDate(Expression, Func):
461    pass
462
463
464class TimeStrToTime(Expression, Func):
465    arg_types = {"this": True, "zone": False}
466
467
468class TimeStrToUnix(Expression, Func):
469    pass
470
471
472class TimeToStr(Expression, Func):
473    arg_types = {"this": True, "format": True, "culture": False, "zone": False}
474
475
476class TimeToTimeStr(Expression, Func):
477    pass
478
479
480class TimeToUnix(Expression, Func):
481    pass
482
483
484class TsOrDiToDi(Expression, Func):
485    pass
486
487
488class TsOrDsToDate(Expression, Func):
489    arg_types = {"this": True, "format": False, "safe": False}
490
491
492class TsOrDsToDateStr(Expression, Func):
493    pass
494
495
496class TsOrDsToDatetime(Expression, Func):
497    pass
498
499
500class TsOrDsToTime(Expression, Func):
501    arg_types = {"this": True, "format": False, "safe": False}
502
503
504class TsOrDsToTimestamp(Expression, Func):
505    pass
506
507
508class UnixDate(Expression, Func):
509    pass
510
511
512class UnixMicros(Expression, Func):
513    pass
514
515
516class UnixMillis(Expression, Func):
517    pass
518
519
520class UnixSeconds(Expression, Func):
521    pass
522
523
524class UnixToStr(Expression, Func):
525    arg_types = {"this": True, "format": False}
526
527
528class UnixToTime(Expression, Func):
529    arg_types = {
530        "this": True,
531        "scale": False,
532        "zone": False,
533        "hours": False,
534        "minutes": False,
535        "format": False,
536        "target_type": False,
537    }
538
539    SECONDS: t.ClassVar[Literal | Neg] = Literal.number(0)
540    DECIS: t.ClassVar[Literal | Neg] = Literal.number(1)
541    CENTIS: t.ClassVar[Literal | Neg] = Literal.number(2)
542    MILLIS: t.ClassVar[Literal | Neg] = Literal.number(3)
543    DECIMILLIS: t.ClassVar[Literal | Neg] = Literal.number(4)
544    CENTIMILLIS: t.ClassVar[Literal | Neg] = Literal.number(5)
545    MICROS: t.ClassVar[Literal | Neg] = Literal.number(6)
546    DECIMICROS: t.ClassVar[Literal | Neg] = Literal.number(7)
547    CENTIMICROS: t.ClassVar[Literal | Neg] = Literal.number(8)
548    NANOS: t.ClassVar[Literal | Neg] = Literal.number(9)
549
550
551class UnixToTimeStr(Expression, Func):
552    pass