Transform Dates and Times

Pull a component out

❯make a new table that takes the events table and extracts the year from the date column

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — dt.year() returns the year as an integer. Every component accessor lives under dt.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["events"],
  output_tables=["events_year"],
)
def get_year(events: TableFrameSpec) -> TableFrameSpec:
  expr = col("date").dt.year()
  return events.with_columns(expr)

⏺

Execution committed, 0 failures.

⏺

subscriptions/events_year is live — the year as an integer:

┌──────┐
│ date │
├──────┤
│ 2024 │
├──────┤
│ 2024 │
└──────┘

Transformed tables

events

date
2024-03-15
2024-11-01

2 rows

events_year

date
2024
2024

2 rows

Shift a date

❯make a new table that takes the events table and moves the date column a week later

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — dt.offset_by("1w") takes a duration string rather than a number, so the unit is explicit in the code.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["events"],
  output_tables=["events_plus_week"],
)
def shift_week(events: TableFrameSpec) -> TableFrameSpec:
  expr = col("date").dt.offset_by("1w")  # Add 1 week
  return events.with_columns(expr)

⏺

Execution committed, 0 failures.

⏺

subscriptions/events_plus_week is live — every date a week later:

┌────────────┐
│    date    │
├────────────┤
│ 2024-03-22 │
├────────────┤
│ 2024-11-08 │
└────────────┘

Transformed tables

events

date
2024-03-15
2024-11-01

2 rows

events_plus_week

date
2024-03-22
2024-11-08

2 rows

Round a timestamp down

❯make a new table that takes the events table and truncates the timestamp column to the hour

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — dt.truncate("1h") zeroes everything finer than the hour. It rounds down always, unlike a round.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["events"],
  output_tables=["events_by_hour"],
)
def truncate_hour(events: TableFrameSpec) -> TableFrameSpec:
  expr = col("timestamp").dt.truncate("1h")  # Truncate to hour
  return events.with_columns(expr)

⏺

Execution committed, 0 failures.

⏺

subscriptions/events_by_hour is live — minutes and seconds zeroed:

┌─────────────────────┐
│      timestamp      │
├─────────────────────┤
│ 2024-03-15 09:00:00 │
├─────────────────────┤
│ 2024-11-01 23:00:00 │
└─────────────────────┘

Transformed tables

events

timestamp
2024-03-15 09:42:17
2024-11-01 23:05:00

2 rows

events_by_hour

timestamp
2024-03-15 09:00:00
2024-11-01 23:00:00

2 rows

Express a duration as a number

❯make a new table that takes the spans table and expresses the duration column in days

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — dt.total_days() converts the whole duration to days rather than reading a days field, so 36 hours is 1.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["spans"],
  output_tables=["spans_days"],
)
def duration_days(spans: TableFrameSpec) -> TableFrameSpec:
  expr = col("duration").dt.total_days()
  return spans.with_columns(expr)

⏺

Execution committed, 0 failures.

⏺

subscriptions/spans_days is live — each duration in whole days:

┌──────────┐
│ duration │
├──────────┤
│ 2        │
├──────────┤
│ 0        │
└──────────┘

Transformed tables

spans

duration
2d 03:00:00
0d 05:30:00

2 rows

spans_days

duration
2
0

2 rows

Work with time zones

❯make a new table that takes the events table and shows the timestamp column in America/New_York

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — dt.convert_time_zone() keeps the same instant and changes how it reads, so the wall-clock digits move.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["events"],
  output_tables=["events_ny"],
)
def to_new_york(events: TableFrameSpec) -> TableFrameSpec:
  expr = col("timestamp").dt.convert_time_zone("America/New_York")
  return events.with_columns(expr)

⏺

Execution committed, 0 failures.

⏺

subscriptions/events_ny is live — the same instants read in New York time:

┌─────────────────────────┐
│        timestamp        │
├─────────────────────────┤
│ 2024-03-15 10:00:00 EDT │
├─────────────────────────┤
│ 2024-11-01 10:00:00 EDT │
└─────────────────────────┘

Transformed tables

events

timestamp
2024-03-15 14:00:00 UTC
2024-11-01 14:00:00 UTC

2 rows

events_ny

timestamp
2024-03-15 10:00:00 EDT
2024-11-01 10:00:00 EDT

2 rows

Format and convert

❯make a new table that takes the events table and formats the date column as text in YYYY-MM-DD

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — dt.to_string() with a format string. This turns the column into text, so no further date operations will work on it.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["events"],
  output_tables=["events_formatted"],
)
def format_date(events: TableFrameSpec) -> TableFrameSpec:
  expr = col("date").dt.to_string("%Y-%m-%d")
  return events.with_columns(expr)

⏺

Execution committed, 0 failures.

⏺

subscriptions/events_formatted is live — the date rendered as text:

┌────────────┐
│    date    │
├────────────┤
│ 2024-03-15 │
├────────────┤
│ 2024-11-01 │
└────────────┘

Transformed tables

events

dateDate
2024-03-15
2024-11-01

2 rows

events_formatted

dateString
2024-03-15
2024-11-01

2 rows