| Author |
Message
|
| jgonz |
 Posted: Wed Feb 03, 2016 7:35 am Post subject: Insert timestamp with local timezone |
 |
|
Novice
Joined: 20 Nov 2015
Posts: 16
|
Hi,
We have a requirement to use TIMESTAMP WITH LOCAL TIMEZONE for storing any timestamps in our database considering that each of the nodes in our cluster are in different timezones.
Keeping performance in mind, I've been trying to find a way to keep using host variables in our SQL statements, but it seems this cannot be done since I cannot get the timezone from a timestamp without casting the timestamp into a character and concatenating the TIMESTAMP keyword which is unusable in a host variable. Is there a way to get this to work?
For illustrative purposes, here's what I would like to achieve:
| Code: |
INSERT INTO Database.TEST_TIMESTAMPS (
TIMESTAMP_, TIMESTAMP_WITH_TIMEZONE, TIMESTAMP_WITH_LOCALTIMEZONE)
VALUES(
CURRENT_TIMESTAMP,
startResult,
endResult); |
But I have only got it to work with a passthru as such:
| Code: |
DECLARE startResult CHAR CAST(startTime AS CHAR FORMAT 'yyyy-MM-dd HH:mm:ss.SSSSSSZZZ');
SET startResult = 'TIMESTAMP ''' || result || '''';
DECLARE endResult CHAR CAST(endTime AS CHAR FORMAT 'yyyy-MM-dd HH:mm:ss.SSSSSSZZZ');
SET endResult = 'TIMESTAMP ''' || result || '''';
DECLARE statement CHAR
'INSERT INTO TEST_TIMESTAMPS (
TIMESTAMP_,
TIMESTAMP_WITH_TIMEZONE,
TIMESTAMP_WITH_LOCALTIMEZONE
) VALUES (' || CAST(CURRENT_TIMESTAMP AS CHAR)
|| ',' || startResult
|| ',' || endResult
|| ')';
PASSTHRU(statement); |
However this as I understand will be inefficient and generate a new prepared statement every time it's executed. My only other option I see is creating a stored procedure and accepting CHAR parameters that would include the timezone and then do a TO_TIMESTAMP on those parameters. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Feb 03, 2016 7:41 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
What types are the database columns? Are those types listed in the conversion table for ESQL and Database types?
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Feb 03, 2016 8:00 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Considering the fact that you do have multiple time zones why not use GMTTIMESTAMP in ESQL? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| jgonz |
| Posted: Wed Feb 03, 2016 8:40 am Post subject: |
|
|
Novice
Joined: 20 Nov 2015
Posts: 16
|
| mqjeff wrote: |
| What types are the database columns? |
The database column is TIMESTAMP(6) WITH LOCAL TIMEZONE.
| mqjeff wrote: |
| Are those types listed in the conversion table for ESQL and Database types? |
Taking a look at the conversion table in Infocenter, no. DATE is the only value that is listed in the conversion table for Oracle. DATE is unacceptable for us because we need millisecond precision and the time zone which date does not provide.
| fjb_saper wrote: |
| Considering the fact that you do have multiple time zones why not use GMTTIMESTAMP in ESQL? |
We are considering this, but thus far it's mainly for consistency. All the other date/times in this database schema are in the local timezone.
EDIT: Also, there are date/times from external systems that are coming in with the timezone format as strings and it would be preferable not to have to manipulate them. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 03, 2016 8:55 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Are you sure the Oracle DATE type doesn't include time information?
If it doesn't, then you'll have to do the cast to string and rely on Oracle to parse into a timestamp. Including the CURRENT_TIMESTAMP
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| maurito |
| Posted: Wed Feb 03, 2016 9:02 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| jgonz wrote: |
The database column is TIMESTAMP(6) WITH LOCAL TIMEZONE.
|
you have not said what database you are using.
for the benefit of the readers, probably it is Oracle. TIMESTAMP with Local timezone is not supported in ESQL. So, either use PASSTHRU or GMTTIMESTAMP and do the conversions yourself. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 03, 2016 9:06 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| maurito wrote: |
| jgonz wrote: |
The database column is TIMESTAMP(6) WITH LOCAL TIMEZONE.
|
you have not said what database you are using.
for the benefit of the readers, probably it is Oracle. TIMESTAMP with Local timezone is not supported in ESQL. So, either use PASSTHRU or GMTTIMESTAMP and do the conversions yourself. |
In fact, jgonz did say it was oracle...
And I assume by "TIMESTAMP with Local timezone is not supported in ESQL", you mean "sending it to an Oracle database is not supported".
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| maurito |
| Posted: Wed Feb 03, 2016 9:12 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| mqjeff wrote: |
| maurito wrote: |
| jgonz wrote: |
The database column is TIMESTAMP(6) WITH LOCAL TIMEZONE.
|
you have not said what database you are using.
for the benefit of the readers, probably it is Oracle. TIMESTAMP with Local timezone is not supported in ESQL. So, either use PASSTHRU or GMTTIMESTAMP and do the conversions yourself. |
In fact, jgonz did say it was oracle...
And I assume by "TIMESTAMP with Local timezone is not supported in ESQL", you mean "sending it to an Oracle database is not supported". |
sorry I missed that ( the oracle bit ).
as for the other part, in ESQL you only have a TIMESTAMP datatype. You can set it to CURRENT_TIMESTAMP or CURRENT_GMTTIMESTAMP.
if you try to insert a timestamp in an oracle column defined as TIMESTAMP with LOCAL TIMEZONE you will get an error.
So the OP needs to use GMTTIMESTAMP as suggested before by fjb_saper |
|
| Back to top |
|
|
| jgonz |
| Posted: Wed Feb 03, 2016 9:28 am Post subject: |
|
|
Novice
Joined: 20 Nov 2015
Posts: 16
|
So, from what I gather from these responses and from my own testing is that executing a SQL statement directly in ESQL to insert into a TIMESTAMP WITH LOCAL TIME ZONE datatype [EDIT: while including the time zone] in Oracle is not possible in WMB8. However, as I knew already from testing, PASSTHRU will work.
In addition to this, while using a PASSTHRU, we cannot use host variables (i.e. 'VALUES (?,?)') and instead need to create a string concatenated with variable values which I understand runs inefficiently.
I therefore intend to create stored procedures for all my SQL queries that insert timestamps into TIMESTAMP WITH LOCAL TIME ZONE columns unless anyone knows of a better solution?
Last edited by jgonz on Wed Feb 03, 2016 11:42 am; edited 1 time in total |
|
| Back to top |
|
|
| maurito |
| Posted: Wed Feb 03, 2016 9:42 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| jgonz wrote: |
| In addition to this, while using a PASSTHRU, we cannot use host variables (i.e. 'VALUES (?,?)') |
why not ? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 03, 2016 10:20 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You can't send a character formatted string to a TIMESTAMP WITH LOCAL TIME ZONE datatype in Oracle?
If not, then I'm confused that passthru works.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| maurito |
| Posted: Wed Feb 03, 2016 11:38 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| mqjeff wrote: |
You can't send a character formatted string to a TIMESTAMP WITH LOCAL TIME ZONE datatype in Oracle?
|
is that a question or a statement ? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 03, 2016 11:39 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| maurito wrote: |
| mqjeff wrote: |
You can't send a character formatted string to a TIMESTAMP WITH LOCAL TIME ZONE datatype in Oracle?
|
is that a question or a statement ? |
A question.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| jgonz |
| Posted: Wed Feb 03, 2016 11:51 am Post subject: |
|
|
Novice
Joined: 20 Nov 2015
Posts: 16
|
| mqjeff wrote: |
You can't send a character formatted string to a TIMESTAMP WITH LOCAL TIME ZONE datatype in Oracle?
If not, then I'm confused that passthru works. |
Sorry, I added an edit to clarify that this does not work when passing a the time zone in the timestamp string. Meaning that while this works:
| Code: |
| INSERT INTO Database.TEST_TIMESTAMPS ( TIMESTAMP_WITH_LOCALTIMEZONE ) VALUES (CAST(CURRENT_TIMESTAMP AS CHAR) |
These do not and give an invalid character error:
| Code: |
| INSERT INTO Database.TEST_TIMESTAMPS ( TIMESTAMP_WITH_LOCALTIMEZONE ) VALUES (CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'I')) |
| Code: |
| INSERT INTO Database.TEST_TIMESTAMPS ( TIMESTAMP_WITH_LOCALTIMEZONE) VALUES( CAST(CURRENT_TIMESTAMP AS CHAR FORMAT 'yyyy-MM-dd HH:mm:ss.SSSZZZ')) |
|
|
| Back to top |
|
|
| jgonz |
| Posted: Wed Feb 03, 2016 11:58 am Post subject: |
|
|
Novice
Joined: 20 Nov 2015
Posts: 16
|
| maurito wrote: |
| jgonz wrote: |
| In addition to this, while using a PASSTHRU, we cannot use host variables (i.e. 'VALUES (?,?)') |
why not ? |
Similar to my above post, I believe this is because when you do something like CAST(CURRENT_TIMESTAMP AS CHAR), the resulting string has the TIMESTAMP keyword preceding it. When this the SQL statement is processed the entire string would read as something like:
TIMESTAMP '2016-02-02 18:11:58.143825'
When you try to concatenate TIMESTAMP keyword to the beginning of a string formatted timestamp, the string reads something like this:
'TIMESTAMP '2016-02-02 18:11:58.143825''
this would occur when using the INSERT statement directly in ESQL or using host variables in a passthru statement. However, when manipulating the entire string for the sql statement, you can remove the ' which is causing the SQL statement to fail. Please correct me if I am mistaken. |
|
| Back to top |
|
|
|
|
