Date Time issue

Hi Experts, My date table is in UTC and date column doesnt have any time stamp in source db. in VDB it is defined as Date column. when I query this table from EST it is converting to EST time zone and showing(Example: Date column--> Mon Feb **20** 19:00:00 EST 2017,, formatdate('yyyy-MM-dd',date column) -->2017-02-**21**). how can I avoid doing these type of conversions ? and I read some where use format data those converts date to text which I dont want because reporting tool need this as date column. I also tried with adding context ('i18n' = 'utc_i18n' ) at end of my query but no luck. Appriciate your help.
user
17-01-2018 11:54:36 -0500

3 Answers

Hi, You're on the right track with the *CONTEXT* clause, but unless you've created it, *utc_i18n* doesn't exist. If you want to create an i18n value for UTC, check out the documentation on [Managing Internationaliztion Configurations](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/advanced_characteristics/creating_new_internationalization_configurations/creating_new_internationalization_configurations#managing-internationalization-configurations). However, there are a couple of simpler items you may want to look at first. (The [FORMATDATE](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#formatdate) function should not be necessary.) The first thing I would look at is the Base View that this date is coming from. Even though you aren't storing a timestamp in the source, the source database has a time zone setting and, depending on the RDBMS, may be storing the date field with a *00:00:00* (midnight) time stamp attached. This midnight timestamp could be getting offset from the intended UTC value before you even query it. On the *Search methods* tab of the *Options* on your Base View, the *i18n* value is likely set to the default value for your VDP server. If this is different than the source, you could set it to match as outlined in the [Internationalization Configuration](https://community.denodo.com/kb/view/document/Internationalization%20configuration%20and%20dates?category=Common+Errors) article on our knowledgebase. Since in your case you don't want the timestamp at all, I'd also take a look at the [Schema of the Base View](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/viewing_the_schema_of_a_base_view) and edit the field type. Even though it has the field type *date*, if you click the edit (pencil) icon next to the drop down, you'll be able to choose whether this field should be represented as a *DATE*, *TIME*, or full *TIMESTAMP*. In your use case, I'd suggest changing it to *DATE*. Depending on what tool(s) you are running your query from, there could be an application level setting forcing a time zone conversion. If you check the Base View settings I mentioned, you should get the results you desire, but your reporting tool or client app could be overriding that. In such a situation, your *CONTEXT* clause specifying an i18n value may be necessary, but as mentioned above, *utc_i18n* would need to be created if you haven't already. Hope this helps!
Denodo Team
22-01-2018 12:29:01 -0500
Why all this complexity? Just give me the option to turn any datetime conversion off.
user
03-12-2018 14:21:44 -0500
Hi, Denodo Platform 6 (the latest version at the time of the prior question and answer) didn't have a concept of a timestamp without a timezone. Thus, there really wasn't a way to "turn off conversion" without converting it to text, as any consuming client application would still see the time zone data as relevant and could perform its own conversion. In version 7.0, this has been improved so as to provide [distinct temporal data types](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/language_for_defining_and_processing_data_vql/data_types/data_types_for_dates_timestamps_and_intervals) for TIME (without date), TIMESTAMPTZ (a full time stamp indicating a moment in time), LOCALDATE (without time), and TIMESTAMP (without time zone). These data formats correspond to the standard ANSI SQL ways of representing time data, and, specifically the last two, provide you with the means to "turn off" datetime conversions, by delivering it to consuming applications without the concept of a time zone. By delivering the data in a LOCALDATE or TIMESTAMP format, Denodo now allows you to maintain a non-text format without attaching time zone detail to the data. Hope this helps!
Denodo Team
03-12-2018 18:39:04 -0500
You must sign in to add an answer. If you do not have an account, you can register here