Summary
Activity
Forums
Tracker
Lists
Tasks
Docs
Surveys
News
SCM
Files

[#1010991] TimeSpan - Interval Mapping Problem -> Error: 22007: invalid syntax for type time: 200.00:00:00

View Trackers | Bugs | Download .csv | Monitor

Date:
2011-02-08 19:51		 Priority:
3
State:
Open
Submitted by:
Damian W (metrix) 		Assigned to:
Nobody (None)
Npgsql Version:
2.0.11		 Category:
Group:
Resolution:
None
Summary:
TimeSpan - Interval Mapping Problem -> Error: 22007: invalid syntax for type time: 200.00:00:00

Detailed description
Hello,
i am using the .Net 4.0 Entity Framework and npgsql 2.0.11.0.

I have a table with an 'interval' field and I have an object which refers to this field as Time in the csdl mapping
file(which basically is a TimeSpan object in c#).

When updating this field, all values up to 23:59:59 are accepted and the SQL looks like this with call_limit as the
DB field:

UPDATE "domain"."gateway" SET "call_limit"=TIME '23:59:59' WHERE "id"=7

Bigger values, like this
UPDATE "domain"."gateway" SET "call_limit"=TIME '24:01:00' WHERE "id"=7
end up in
FEHLER: Datum/Zeit-Feldwert ist außerhalb des gültigen Bereichs: »24:01:00«
LINE 1: ...name"='G-DT-1',"type"='Account',"call_limit"=TIME '24:01:00'...
something like "the value is outside of the valid area" error message.

I also place values like 200 days in my Timespan object, which end up in the following SQL:
UPDATE "domain"."gateway" SET "call_limit"=TIME '200.00:00:00' WHERE "id"=7
This causes the same error as the above one.

I think that this is a database problem which can be avoided by another sql statement like this using implicit casting:
UPDATE "domain"."gateway" SET "call_limit"='23:59:59'::interval WHERE "id"=7
UPDATE "domain"."gateway" SET "call_limit"='24:01:59'::interval WHERE "id"=7
UPDATE "domain"."gateway" SET "call_limit"='200.00:00:00'::interval WHERE
"id"=7

These queries work.


Here are some additional information about the error:
File: .\src\backend\utils\adt\datetime.c
Line: 3557

Stacktrace:
bei Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()
bei Npgsql.ForwardsOnlyDataReader.GetNextResponseObject()
bei Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
bei Npgsql.ForwardsOnlyDataReader.NextResult()
bei Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command,
NotificationThreadBlock threadBlock, Boolean synchOnReadError)
bei Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
bei Npgsql.NpgsqlCommand.ExecuteNonQuery()
bei System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection
connection, Dictionary`2 identifierValues, List`1 generatedValues)
bei System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter
adapter)

Followup

Message
Date: 2011-02-22 17:09
Sender: Damian W

the column type is interval. i'm using the entity framework
to get the objects persisted in the database, so i cannot
(or: will not) write my own sql-queries.
the mapper seems to use TIME instead of INTERVAL when
inserting values, so that it creates an INSERT INTO tbl
(col1) VALUE (TIME '24:01:00'); which in fact throws an
error but INTERVAL '24:01:00' would work.
Date: 2011-02-22 13:28
Sender: Alex A Ermoshenko

In my case

select TIME '23:59:59'; --23:59:59
select TIME '24:01:00'; --ERROR: date/time field value out of range: "24:01:00"
select INTERVAL '23:59:59'; --23:59:59
select INTERVAL '24:01:00'; --24:01:00
select version();--"PostgreSQL 9.0.3, compiled by Visual C++ build 1500, 64-bit"

How type of field "call_limit" in table "domain"."gateway"? Interval?

Attached Files:

Changes:

Field Old Value Date By
summaryTimeSpan - Interval Mapping Problem -> Error: 22007: invalid syntax for type time: 200.00:00:00 2011-02-22 17:09metrix
Powered By FusionForge