RE: Timestamps

[Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>]

It appears to be using the system values...

My QUTCOFFSET is currently -4:00 because the eastern US is on Eastern Daylight Savings Time
The value of "current timezone" is -40000 (4 hours, 00 minutes, 00 seconds)

This works perfectly
    Exec Sql 
      Set :tokenExpire = Timestamp(Translate(:credentials.expiration, ' ', 'TZ')) +
                         current timezone;



-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jon Paris
Sent: Friday, July 22, 2022 11:42 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Timestamps

Interesting.

It was actually whether it used the system values from CEEUTCO that I was wondering about.


Jon P.

> On Jul 22, 2022, at 11:25 AM, Daniel Gross <daniel@xxxxxxxx> wrote:
>
> Hi Jon,
>
> > Do these SQL approaches deal with the UTCV offset?
>
> I tested JSON_TABLE with offsets like „+02:00“ and „-01:00“ - and the resulting timestamp column is then in UTC. 
>
> Regards,
> Daniel 
>
>
>
> > > On Jul 22, 2022, at 5:05 AM, Daniel Gross <daniel@xxxxxxxx> wrote:
> > >
> > > And SQL has another tool to offer - JSON_TABLE can handle ISO-8601 timestamps … not complete, but ..
> > >
> > > select tstamp
> > > from json_table( '{"ts":"2022-07-22T12:34:56.0+00:00"}‘, 
> > >               '$' columns (tstamp timestamp path '$.ts'));
> > >
> > > But sadly, JSON_TABLE doesn’t implement ISO-8601 completely - so you would have to append „.microseconds“ and replace „Z“ with „+00:00“ … and of course you have to wrap your timestamp up in some sort of JSON structure like in the example above.
> > >
> > > HTH
> > > Daniel
> > >
> > >
> > >
> > > > > Am 22.07.2022 um 07:58 schrieb Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>:
> > > >
> > > > AFAIK there is no direct conversion (neither in RPG nor in SQL) for this
> > > > format.
> > > > In pure RPG you have to deal with %XLATE and/or %SCANRPL to convert it into
> > > > a valid character representation of the timestam and then convert the
> > > > result.
> > > > With SQL it is a little easier, but you still have to remove T and Z:
> > > > Exec SQL  SET :YourTimestamp = Timestamp(Translate('2022-07-21T20:14:36Z', '
> > > > ', 'TZ'));
> > > >
> > > > Mit freundlichen Grüßen / Best regards
> > > >
> > > > Birgitta Hauser
> > > > Modernization – Education – Consulting on IBM i
> > > >
> > > >
> > > > "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> > > > Brown)
> > > > "If you think education is expensive, try ignorance." (Derek Bok)
> > > > "What is worse than training your staff and losing them? Not training them
> > > > and keeping them!"
> > > > „Train people well enough so they can leave, treat them well enough so they
> > > > don't want to.“ (Richard Branson)
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
> > > > Wilburn
> > > > Sent: Donnerstag, 21. Juli 2022 22:49
> > > > To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
> > > > Subject: Timestamps
> > > >
> > > > Is there a slick way to compare the current UTC time with a character
> > > > "timestamp" value like this?
> > > > '2022-07-21T20:14:36Z'
> > > >
> > > > This is for the token expiration for AWS.  The timestamp formats for the
> > > > various services are different.
> > > >
> > > > I was hoping not to have to do it manually by %SUBST or %SCANRPL (replacing
> > > > T and colons with period and removing the Z).
> > > >
> > > > TIA
> > > > Greg
> > > >
> > > > [Logo]<https://www.totalbizfulfillment.com/>    Greg Wilburn
> > > > Director of IT
> > > > 301.895.3792 ext. 1231
> > > > 301.895.3895 direct
> > > > gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
> > > > 1 Corporate Dr
> > > > Grantsville, MD 21536
> > > > www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
> > > > --
> > > > This is the RPG programming on IBM i (RPG400-L) mailing list To post a
> > > > message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
> > > > change list options,
> > > > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > > > or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> > > > Before posting, please take a moment to review the archives at
> > > > https://archive.midrange.com/rpg400-l.
> > > >
> > > > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> > > > questions.
> > > >
> > > > Help support midrange.com by shopping at amazon.com with our affiliate link:
> > > > https://amazon.midrange.com
> > > >
> > > > -- 
> > > > This is the RPG programming on IBM i (RPG400-L) mailing list
> > > > To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > > > or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> > > > Before posting, please take a moment to review the archives
> > > > at https://archive.midrange.com/rpg400-l.
> > > >
> > > > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
> > > >
> > > > Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com
> > >
> > > -- 
> > > This is the RPG programming on IBM i (RPG400-L) mailing list
> > > To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > > or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at https://archive.midrange.com/rpg400-l.
> > >
> > > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
> > >
> > > Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com
> >
> > -- 
> > This is the RPG programming on IBM i (RPG400-L) mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/rpg400-l.
> >
> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com
>
> -- 
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com