Postgres and DATE_TRUNC function too many arguments

mortenmoulder commented 3 months ago

Just updated to 0.7.0 and it looks really good. Good job!

Trying something as simple as:

readings
    .Where(x => x.timestamp >= DateTime.UtcNow.Date)

throws this error:

Could not serialize object to HTML. Npgsql.PostgresException (0x80004005): 42883: function date_trunc(unknown, timestamp with time zone, unknown) does not exist
...
...
  Exception data:
    Severity: ERROR
    SqlState: 42883
    MessageText: function date_trunc(unknown, timestamp with time zone, unknown) does not exist
    Hint: No function matches the given name and argument types. You might need to add explicit type casts.
    Position: 117
    File: parse_func.c
    Line: 621
    Routine: ParseFuncOrColumn

Generated SQL is:

SELECT r.deviceid, r.facility, r.id, r.payload, r.tags, r.timestamp, r.type
FROM readings AS r
WHERE r.timestamp >= date_trunc('day', now(), 'UTC')

The problem is DATE_TRUNC only takes two arguments, so the function call should look like something like this instead:

DATE_TRUNC('day', now() AS TIME ZONE 'UTC')

tareqimbasher commented 2 months ago

@mortenmoulder sorry for the late reply!

This is an NpgSql issue and not necessarily one that NetPad can do anything about. What's most likely happening is that your timestamp column is of type timestamp with time zone. When you compare it using UtcNow your NpgSql passes your UTC date argument as type timestamp without time zone resulting in a mismatch and not finding a function that can compare the two different types. If you change that UtcNow to just Now it should work.

You can read more about this here:

Just updated to 0.7.0 and it looks really good. Good job

Thank you for the support! Glad you're enjoying it :smiley:

tareqimbasher commented 1 month ago

Closing this issue, please feel free to reopen it if your issue is not resolved.

tareqimbasher / NetPad

Postgres and DATE_TRUNC function too many arguments #191