When CAST a TIMESTAMP(x) value AS TIMESTAMP(y), and x > y, the decimal part that exceeds the accuracy will be directly ignored instead of rounding.

[DDDDDstar]

What happens?

When CAST a TIMESTAMP(x) value AS TIMESTAMP(y), and x > y, the decimal part that exceeds the accuracy will be directly ignored instead of rounding.

To Reproduce

select cast('2024-05-10 11:06:33.846' as timestamp(0)) from t;

The expected result is '2024-05-10 11:06:34', while the actual result is '2024-05-10 11:06:33'.

OS:

macos

DuckDB Version:

v0.10.2

DuckDB Client:

Command line terminal

Full Name:

Shixin Wan

Affiliation:

ApeCloud

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[soerenwolfers]

EDIT: misread the results. PostgreSQL does round up.

This behavior is

• consistent with DECIMAL:

SELECT
33.846::DECIMAL(4, 2)

┌──────────────────────────────┐
│ CAST(33.846 AS DECIMAL(4,2)) │
│         decimal(4,2)         │
├──────────────────────────────┤
│                        33.84 │
└──────────────────────────────┘

• inconsistent with PostgreSQL: https://www.db-fiddle.com/f/8K9U6dudJLxsVujbZFHMkQ/0

[hawkfish]

Decimal would be a separate issue, but yes PG does round timestamps:

hawkfish=# select cast('2024-05-10 11:06:33.846' as timestamp(0));
      timestamp      
---------------------
 2024-05-10 11:06:34