[teiid-issues] [JBoss JIRA] (TEIID-5672) When using DateTime datatype with millisecond fractions, i.e DateTime(3) the resulting urls for the records are invalid

[Christoph John (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13703479#comment-13703479 ] 

Christoph John commented on TEIID-5672:
---------------------------------------

Hello Ramesh

https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

DATETIME(0) means the mysql database table uses just year,month,day,hour,minutes and seconds
- internal format = "2019-03-04 13:33:33"
- format in odata = "2019-03-04T18:54:28+01:00"
DATETIME(3) means the mysql database tables also uses milliseconds. 3 is the number of post comma fractions. Can be up to microseconds = DATETIME(6)

-internal format for DATETIME(3)  "2019-03-04 13:33:33.123"
- format in odata "2019-03-04T18:54:28.455+01:00"

Please note, when working with DATETIME(3) I can use it with filtering for DATETIME RANGES and I can add records with a DATETIME(3) key to the database. Only when I send a select query containing a DATETIME(3) value, I get an error.


> When using DateTime datatype with millisecond fractions, i.e DateTime(3) the resulting urls for the records are invalid
> -----------------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5672
>                 URL: https://issues.jboss.org/browse/TEIID-5672
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 11.2.1
>         Environment: Docker Container
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Major
>
> I am using a composite key based on a BIGINT and a DATETIME(3) value. The URLs which Teiid generates are invalid in this case. When using DATETIME(0) the links or ok. Example of generated record structure
> {code:java}
> <a:feed xmlns:a="http://www.w3.org/2005/Atom" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns:d="http://docs.oasis-open.org/odata/ns/data" m:context="https://morpheus.fritz.box/odata4/svc/my_nutri_diary/$metadata#Diary">
> <a:id>
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary
> </a:id>
> <a:entry>
> <a:id>
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(fkProfile=2,AddedDateTime=2019-03-03T17:53:12.676Z)
> </a:id>
> <a:title/>
> <a:summary/>
> <a:updated>2019-03-03T17:53:52Z</a:updated>
> <a:author>
> <a:name/>
> </a:author>
> <a:link rel="edit" href="https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(fkProfile=2,AddedDateTime=2019-03-03T17:53:12.676Z)"/>
> <a:link rel="http://docs.oasis-open.org/odata/ns/related/fkDiaryToAccount" title="fkDiaryToAccount" href="https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(fkProfile=2,AddedDateTime=2019-03-03T17:53:12.676Z)/fkDiaryToAccount"/>
> <a:link rel="http://docs.oasis-open.org/odata/ns/related/fkDiaryToFDBProducts" title="fkDiaryToFDBProducts" href="https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(fkProfile=2,AddedDateTime=2019-03-03T17:53:12.676Z)/fkDiaryToFDBProducts"/>
> <a:link rel="http://docs.oasis-open.org/odata/ns/relatedlinks/fkDiaryToAccount" title="fkDiaryToAccount" href="https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(fkProfile=2,AddedDateTime=2019-03-03T17:53:12.676Z)/fkDiaryToAccount/$ref"/>
> <a:link rel="http://docs.oasis-open.org/odata/ns/relatedlinks/fkDiaryToFDBProducts" title="fkDiaryToFDBProducts" href="https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(fkProfile=2,AddedDateTime=2019-03-03T17:53:12.676Z)/fkDiaryToFDBProducts/$ref"/>
> <a:category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="#svc.1.my_nutri_diary.Diary"/>
> <a:content type="application/xml">
> <m:properties>
> <d:fkProfile m:type="Int64">2</d:fkProfile>
> <d:AddedDateTime m:type="DateTimeOffset">2019-03-03T17:53:12.676Z</d:AddedDateTime>
> <d:fkIdCode m:type="Int64">1772</d:fkIdCode>
> <d:product_name>Mélange fruits secs</d:product_name>
> <d:brands>Agrosourcing</d:brands>
> <d:DatabaseID m:type="Int16">0</d:DatabaseID>
> <d:MealNumber>5</d:MealNumber>
> <d:AmountInG m:type="Double">100.0</d:AmountInG>
> <d:Calories m:type="Double">0.0</d:Calories>
> <d:ProteinsInG m:type="Double">-1.0</d:ProteinsInG>
> <d:CarbohydratesInG m:type="Double">-1.0</d:CarbohydratesInG>
> <d:SugarsInG m:type="Double">-1.0</d:SugarsInG>
> <d:FatsInG m:type="Double">-1.0</d:FatsInG>
> <d:SaturatedFatsInG m:type="Double">-1.0</d:SaturatedFatsInG>
> <d:SaltsInG m:type="Double">-1.0</d:SaltsInG>
> <d:Diarycol m:null="true"/>
> </m:properties>
> </a:content>
> </a:entry>
> {code}
> When I navigate to 
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary(fkProfile=2,AddedDateTime=2019-03-03T17:53:12.676Z)
> I get HTTP ERROR 404 page not found. Note: I replaced ":"with "%3A" (urlencode) in the url which was required to work with the DateTime(0) primary keys. But this did not help either.



--
This message was sent by Atlassian Jira
(v7.12.1#712002)