Date utility functions¶
We provide a set of functions for the datetime (date and time) operations based on the datetime python library. These functions have the same behavior as the functions provided by the formula column of SeaTable.
Function import required
To use these functions, the dateutils module must be imported.
Introduction¶
Date and time formatting¶
The ISO format is used in date methods, both for input and output, which means:
-
YYYY-MM-DD(or%Y-%m-%dreferring to the python datetime library format codes) for dates -
YYYY-MM-DD HH:mm:ss(or%Y-%m-%d %H:%M:%S) for datetimes. Please note that the hour is (24-hour based) -
Datetimes format with timezone info requires a specific format:
YYYY-MM-DDTHH:mm:ss±hh:mmwith the letterTseparating the date from the time and±hh:mmrepresenting the offset to UTC (here+08:00for UTC+8)
Of course, methods outputs with this format can be reused as input for other dateutils methods requiring the same format. You'll find below an overview example. Every methods are detailed in the following of this section.
Timezone
If the input time string has a timezone info, it will be automatically converted to local time.
Overview example¶
In this example as in the following of this section, the comment at the end of each line shows the expected result (what you should update if you print the result of the current line)
from seatable_api.date_utils import dateutils
dt_now = dateutils.now() # 2025-09-30 15:47:00
# 1. date 10 days after dt_now
dt_10_days = dateutils.dateadd(dt_now, 10) # 2025-10-10 15:47:00
# 2. month 10 days after dt_now
dt_month_10_days = dateutils.month(dt_10_days) # 10
# 3. difference between 2 days
dt_10_days_before = dateutils.dateadd(dt_now, -10)
date_df = dateutils.datediff(dt_10_days_before, dt_10_days, unit="D") # 20
# 4. handle the time string with time-zone info with local timezone of "Asia/Shanghai" (UTC+8)
time_str = "2025-07-17T21:57:41+08:00"
time_day = dateutils.day(time_str) # 17
time_month = dateutils.month(time_str) # 7
time_year = dateutils.year(time_str) # 2025
time_hour = dateutils.hour(time_str) # 15 (! if local timezone is UTC+2 !)
time_minute = dateutils.minute(time_str) # 57
time_date = dateutils.date(time_year, time_month, time_day) # 2025-07-17
Dealing with date and time¶
date¶
date
Return the ISO formatted date string.
Example
dateadd¶
dateadd
Add a number of a specified interval to a datetime datetime_str. interval can represent the following units: years, months, weeks, days, hours, minutes and seconds (default is days). Negative values can be used to subtract from datetime_str.
Example
from seatable_api.date_utils import dateutils
date_str = "2025-9-15"
datetime_str = "2025-9-15 15:23:21"
dateutils.dateadd(date_str, -2, 'years') # 2023-09-15
dateutils.dateadd(date_str, 3, 'months') # 2025-12-15
dateutils.dateadd(datetime_str, 44, 'minutes') # 2025-09-15 16:07:21
dateutils.dateadd(datetime_str, 1000, 'days') # 2028-06-11 15:23:21
dateutils.dateadd(datetime_str, 3, 'weeks') # 2025-10-06 15:23:21
dateutils.dateadd(datetime_str, -3, 'hours') # 2025-09-15 12:23:21
dateutils.dateadd(datetime_str, 3, 'seconds') # 2025-09-15 15:23:24
datediff¶
datediff
Compute the time between two datetimes in one of the following units:S, Y, D, H, M, YM, MD, YD. The result can be negative if end_date is before start_date.
For date units (Y,M and D), unit might include two characters:
YM: The difference between the months instart_dateandend_date. The days and years of the dates are ignored.MD: The difference between the days instart_dateandend_date. The months and years of the dates are ignored.YD: The difference between the days ofstart_dateandend_date. The years of the dates are ignored.
dateutils.datediff(start=start_date, end=end_date, unit=datetime_unit)
dateutils.datediff(start_date, end_date, datetime_unit) # (1)!
- Depending on your preferences, you can either specify the name of each parameter (longer but easier to reread) or not
Example
from seatable_api.date_utils import dateutils as dt # (1)!
start_date = "2025-5-16"
end_date = "2026-5-15"
dt.datediff(start=start_date, end=end_date, unit='S') # 31449600 (seconds)
dt.datediff(start=start_date, end=end_date, unit='Y') # 0 (years)
dt.datediff(start=start_date, end=end_date, unit='D') # 364 (days)
dt.datediff(start=start_date, end=end_date, unit='H') # 8736 (hours)
dt.datediff(start=start_date, end=end_date, unit='M') # 12 (months) (from 2025-5 to 2026-5)
dt.datediff(start=start_date, end=end_date, unit='YM') # 0 (months) (from May to May)
dt.datediff(start=start_date, end=end_date, unit='MD') # -1 (days) (from 16 of 15)
dt.datediff("2025-1-28","2026-2-1", unit='YD') # 4 (days) (from January 28 to February 1)
- To make calls shorter or more explicit, feel free to use an alternative name using
askeyword. Here, we usedtinstead of the defaultdateutils
day¶
day
Return the day of a given date.
Example
days¶
days
Return the days difference between two given date. The result can be negative if end is before start.
Example
eomonth¶
eomonth
Return the ISO formatted last day of the nth month before or after given date (depending on the sign of n).
Example
from seatable_api.date_utils import dateutils
date = "2025-7-4"
dateutils.eomonth(date, months=0) # 2025-07-31 (months=0 => current month)
dateutils.eomonth(date, months=2) # 2025-09-30 (2 months after July => September)
dateutils.eomonth(date, months=-5) # 2025-02-28 (5 months before July => February)
hour¶
hour
Return the hour of a given datetime.
Example
hours¶
hours
Return the hours difference between two given datetime. The result can be negative if end is before start.
Example
minute¶
minute
Return the minutes of a given datetime.
Example
month¶
month
Return the month of a given date. The month number starts at 1, like when writing a date.
Example
isomonth¶
isomonth
Return the ISO formatted (YYYY-MM) month of a given date.
Example
months¶
months
Return the months difference between two given date. The result can be negative if end is before start.
Example
now¶
now
Return the ISO formatted current date and time,accurate to seconds.
Example
second¶
second
Return the seconds of given datetime.
Example
today¶
today
Return the ISO formatted current date time in string
Example
weekday¶
weekday
Return the weekday of a date. The result (from 0 to 6) consider a week starting on Monday (returns 0) and ending on Sunday (returns 6).
Example
isoweekday¶
isoweekday
Return the weekday of a date from 1 to 7 and considering a week starting on Monday (based on ISO standard).
Example
weeknum¶
weeknum
Return the week number of a given date, considering the week including January 1st as the first week.
Example
isoweeknum¶
isoweeknum
Return the week number of a given date based on ISO standard.
Example
year¶
year
Return the year of a given date.
Example
Dealing with quarters¶
A specific DateQuarter object exists to deal with quarters. The operations/properties/methods available this object are presented below.
quarter_from_yq¶
quarter_from_yq
Return a DateQuarter object, from a given year and quarter (1 to 4 for current year). if quarter is n less than 1 (or n greater than 4), the returned DateQuarter object will correspond to the year and quarter shifted by n quarters before the first quarter (or n quarters after the fourth quarter) of the year.
Example
quarter_from_ym¶
quarter_from_ym
Return a DateQuarter object, for specified year and month.
Example
to_quarter¶
to_quarter
Return a DateQuarter object from an ISO formatted date or datetime string datetime_str.
Example
quarters_within¶
quarters_within
Return a generator which will generate the DateQuater objects between a start date and an end date. The last (not necessarily full) quarter isn't included by default. You can get it in the generator if you set param include_last to True (False by default).
Example
from seatable_api.date_utils import dateutils
qs1 = dateutils.quarters_within("2024-03-28", "2025-07-17")
print(list(qs1)) # [<DateQuarter-2024,1Q>, <DateQuarter-2024,2Q>,...., <DateQuarter-2025,2Q>]
qs2 = dateutils.quarters_within("2024-03-28", "2025-07-17", include_last=True)
print(list(qs2)) # [<DateQuarter-2024,1Q>, <DateQuarter-2024,2Q>,...., <DateQuarter-2025,2Q>, <DateQuarter-2025,3Q>]
DateQuarter properties and methods¶
Some operations/properties/methods are available for DateQuarter objects.
DateQuarter properties and methods
year: The year of the considered DateQuarterquarter: The quarter of the considered DateQuarter (1 to 4)start_date: The ISO formatted first day of the considered DateQuarterend_date: The ISO formatted last day of the considered DateQuarterdays(): A generator, which will generate every dates (datetime.dateobjects) in the considered DateQuarter
Example
DateQuarter operations¶
DateQuarter operations
Classical operators are available for DateQuarter objects:
- Arithmetic operators:
+(adds a number of quarters to a DateQuarter object),-(returns the number of quarters between two quarters, or the quarter shifted by the number of quarters if used with a number) - Comparison operators:
<,<=,==,>=,>,!= - Membership operators:
in,not in
Example