Format string %Y-%m-%d %-I:%M%p %Z is invalid (Python 3.11.5)

[Bilbottom]

Summary

When I run sqlmesh run with SQLMesh 0.141.1 and Python 3.11.5, it returns the following error:

...

 File "...\Lib\site-packages\sqlmesh\utils\date.py", line 431, in format_tz_datetime
    output_datetime.strftime(format_string)
ValueError: Invalid format string

I tweaked the format_tz_datetime function in my environment to print the values it was trying to use, which are:

output_datetime:  datetime.datetime(2024, 12, 22, 0, 0, tzinfo=datetime.timezone.utc)
format_string:    '%Y-%m-%d %-I:%M%p %Z'

The error seems to be coming from the datetime library because of the %Y-%m-%d %-I:%M%p %Z timestamp defined at:

• https://github.com/TobikoData/sqlmesh/blame/ccb876710204269fede84b32f2df5742fcc1e83e/sqlmesh/utils/date.py#L423

MWE

Since the underlying error seems to be coming from the datetime library, here's a pure Python MWE to replicate the error with this timestamp:

import datetime

output_datetime = datetime.datetime(2024, 12, 22, 0, 0, tzinfo=datetime.timezone.utc)
format_string = "%Y-%m-%d %-I:%M%p %Z"

print("output_datetime:", repr(output_datetime))
print("format_string:", repr(format_string))

# fails here
print(output_datetime.strftime(format_string))

Environment details

• OS: Windows 10
• Python version: 3.11.5
• SQLMesh version: 0.141.1

Comparison to 0.139.0

To confirm that this is indeed a new issue, I took the following steps:

• Rebuilt my pipeline from scratch locally using SQLMesh 0.139.0
• Applied the plan successfully (sqlmesh plan)
• Ran sqlmesh run without error (just to confirm that the command works)
• Upgraded to SQLMesh 0.141.1 (pip install sqlmesh==0.141.1)
• Upgraded to the latest SQLMesh migration (sqlmesh migrate)
• Ran sqlmesh run again, but hit the error

[treysp]

Hello - thank you for reporting this and for a thorough description!

Your example runs without error for me, so based on the Python docs I suspect the issue is differences in strftime support across OSs.

I think the minus sign - in %-I may be the culprit here. Could you check whether this modified formatting string runs without error?

import datetime

output_datetime = datetime.datetime(2024, 12, 22, 0, 0, tzinfo=datetime.timezone.utc)
format_string = "%Y-%m-%d %I:%M%p %Z" # <------ REMOVED `-` from original `%-I`

print("output_datetime:", repr(output_datetime))
print("format_string:", repr(format_string))

# fails here
print(output_datetime.strftime(format_string))

[Bilbottom]

@treysp thanks for the quick response -- I tried your modified version, and that works fine for me 😄

[fdcastel]

@treysp I've encountered this issue as well.

While a new version with the fix is not yet available, is there a workaround involving adjustments to the SQLMesh configuration?

The documentation explains how to set the date format for INCREMENTAL_BY_TIME_RANGE. However, I'm using SCD_TYPE_2_BY_COLUMN, which uses updated_at_name.