Hi,
when querying a DATE column within a query from a Postgres Table it does show time values which are not present in the data 2021-01-06 00:00:00 , even the cur_date() function as agregate returns a date and time value, while a simple select cur_date() will just return the date value.
Is this expected behaviour, a setting, a feature, or a bug?
Bart
What Tool or User Interface are you seeing this behaviour with as date columns in PostgreSQL are mapping to the date columns in Virtuoso, as can be seen by querying linked PostgreSQL tables in Virtuoso from its low level command line isql tool or ODBC interface:
isql:
SQL> select top 2 order_id, order_date from postgres12.northwind.orders;
order_id order_date
SMALLINT NOT NULL DATE
_______________________________________________________________________________
10248 1996.7.4
10249 1996.7.5
2 Rows. -- 28 msec.
SQL> select curdate();
curdate
DATE
_______________________________________________________________________________
2021.1.6
1 Rows. -- 22 msec.
SQL>
ODBC:
SQL> select top 2 order_id, order_date from postgres12.northwind.orders
order_id|order_date
--------+----------
10248 |1996-07-04
10249 |1996-07-05
result set 1 returned 2 rows.
SQL>select curdate()
curdate
----------
2021-01-06
result set 1 returned 1 rows.
SQL>
Might you be using the Virtuoso Conductor UI which seems to use a Datetime field for returning all data types (ie date, datetime, timestamp etc) in and pads the date value time values with “0”'s
Although the isql and ODBC interface output show the date values are being stored correctly.
I indeed used the conductor, you are right, isql shows only the date.
However if we use the same table in to create a R2RML mapping, the triples now have the same date + zero time notation
{ "sub": { "type": "uri", "value": "https://example.com/testresource" } , "pred": { "type": "uri", "value": "http://schema.org/dateModified" } , "obj": { "type": "typed-literal", "datatype": "http://www.w3.org/2001/XMLSchema#dateTime", "value": "2020-11-19T00:00:00" }},
Where the source column is of the type date, R2RML mapping contains: rr:predicateObjectMap [ rr:predicate sch:dateModified ; rr:objectMap [ rr:column "adr_modified_date"^^xsd:date ; ]; ];
Your JSON output is showing the type being used to display the date value in the browser, as was the case when querying the SQL table, where in the browser output a datetime field is being used to display all date datatypes (ie date, datetime, timestamp etc) and pads the date value time elements with “0”'s.
If you add BIND (DATATYPE(?o) as ?datatype) to the query to return the date object values datatype stored in the Virtuoso RDF Quad Store you will see it is xsd:date.
Once again, for a RDFView of the northwind database orders table in PostgreSQL :
isql:
SQL> SPARQL SELECT * FROM <http://demo.openlinksw.com/PostgresDate#> WHERE {?s <http://demo.openlinksw.com/schemas/PostgresDate/order_date> ?o BIND (DATATYPE(?o) as ?datatype)} limit 1;
s o datatype
VARCHAR DATE VARCHAR NOT NULL
_______________________________________________________________________________
http://demo.openlinksw.com/PostgresDate/orders/order_id/10248#this 1996.7.4 http://www.w3.org/2001/XMLSchema#date
1 Rows. -- 9 msec.
SQL>
ODBC:
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0815.0106
Driver: 08.03.3313 OpenLink Virtuoso ODBC Driver (virtodbc.so)
SQL>SPARQL SELECT * FROM <http://demo.openlinksw.com/PostgresDate#> WHERE {?s <http://demo.openlinksw.com/schemas/PostgresDate/order_date> ?o BIND (DATATYPE(?o) as ?datatype)} limit 1
s | o | datatype
--------------------+----------+--------------
http://demo.openlinksw.com/Pos | 1996-07-04 | http://www.w3.org/2001/XMLSchema#date
result set 1 returned 1 rows.
SQL>
SPARQL endpoint JSON output:
{ "head": { "link": [], "vars": ["s", "o", "datatype"] },
"results": { "distinct": false, "ordered": true, "bindings": [
{ "s": { "type": "uri", "value": "http://demo.openlinksw.com/PostgresDate/orders/order_id/10248#this" } , "o": { "type": "typed-literal", "datatype": "http://www.w3.org/2001/XMLSchema#dateTime", "value": "1996-07-04T00:00:00" } , "datatype": { "type": "uri", "value": "http://www.w3.org/2001/XMLSchema#date" }} ] } }
I see, this is starting to get really confusing.
Thanks for the explanation
It isn’t confusing 
This is all about operating on dates represented in a variety of ways.
An R2RML document describes how you want to map relations represented in N-Tuple form to relations represented in RDF 3-Tuple form. Thus, you can simply make a SQL Query or View that represents your N-Tuples, and then describe (via R2RML ) how they are mapped to RDF 3-Tuples using terms from your preferred ontology.