You can translate the question and the replies:

timestamp datatype in denodo

Hi team, As per my understanding timestamp datatype should return data upto milisec: TIMESTAMP Timestamp without a time zone (year, month, day, hour, minute, second and fraction of second with nanosecond precision) When i am creating a base view and the columns are defined as timestamp VDP is displaying data upto seconds only and ignoring the milisec: 2013-09-26 12:40:07 SQL source: 2013-09-26 12:40:07.0000 So why is this happening? This value should flow in as is ? why the milisec values are being truncated? Thanks!
user
02-06-2021 16:59:10 -0400

6 Answers

Hi, By default, Virtual DataPort Server returns all required data from source, although the Virtual DataPort Administration tool does not show it. That is even though Virtual DataPort Administration tool does not show the trailing zero’s Virtual DataPort has the information as it is coming from source. For your scenario, you could add the following property in the file **VDBAdminConfiguration.properties** located under "<DENODO_HOME>\conf\vdp-admin" directory: `com.denodo.vdb.admin.stripTrailingZeros=false` * Then be sure to run **regenerateFiles.bat** to propagate changes across all configuration files. * After making this change, please restart the Virtual DataPort Server. * Then from the Virtual DataPort Administration tool execute the view and check if you are able to see trailing zeros. * Alternatively, you could also change the data type as text and see if the trailing zeros are displayed as expected. You can take a look at [**Date and Time Pattern Strings**](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/appendix/date_and_time_pattern_strings/date_and_time_pattern_strings) section of Virtual DataPort VQL Guide for more information. Hope this helps!
Denodo Team
03-06-2021 08:14:47 -0400
Thank you. I did tried the mentioned the above property changes and still it doesnt show the trailing zeros even for timestamp or for decimal data types. Any thing to try on? Thanks!
user
03-06-2021 11:47:23 -0400
After environment refresh (all latests applied) + property changes we could now see the decimal places for decimal data types. We still dont see the millisec precision for timestamp columns. Any help would be apprecaited.
user
07-06-2021 10:17:19 -0400
Hi, When i tried accessing a SQL Server table from Virtual DataPort, I was able to view the timestamp column with millisecond precision such as '2021-04-02 13:53:35.313'. For your scenario, you could create a **selection view** over the base view by navigating to "File > New > Selection" and use **datetime** functions for the particular column in the 'Output tab' of the derived view. For example, you could use the to_timestamp function as mentioned below, > TO_TIMESTAMP('yyyy-MM-dd''T''HH:mm:ss.SSS',column_name) This way you will be able to display the timestamp column values with milliseconds. You can refer to **[Datetime Functions](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/functions/datetime_functions/datetime_functions)** of Virtual DataPort VQL Guide for more information. Hope this helps!
Denodo Team
10-06-2021 04:41:40 -0400
Thank you ! I tried this as well and still millisec value is not being dispalyed in VDP. Is there anything else that i can try? Also I tried importing a table from SQL and milisec values are not being displayed in VDP. Is there any config property changes required to display millisec in timetsamp fields? Thanks!!
user
10-06-2021 13:11:25 -0400
Instead this works : formatdate('yyyy-MM-dd''T''HH:mm:ss.SSS', <datecoulmn>)
user
10-06-2021 17:28:46 -0400
You must sign in to add an answer. If you do not have an account, you can register here