RE: LONGTODATE and DATETOLONG

Or, just build a UDF to do it. You could use SQL or RPG date/time functions.


Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jack Woehr via MIDRANGE-L
Sent: Monday, May 29, 2023 10:11 PM
To: Don Brown <DBrown@xxxxxxxxxx>
Cc: Jack Woehr <jwoehr@xxxxxxxxxxxxxxxxxxxxxxxx>; Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>; MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>
Subject: Re: LONGTODATE and DATETOLONG

Don, does something like these postgresql functions work? Found here
<https://www.postgresql.org/docs/current/functions-datetime.html>

to_timestamp ( double precision ) → timestamp with time zone

Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with
time zone

to_timestamp(1284352323) → 2010-09-13 04:32:03+00

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16
20:38:40.12-08');*Result: *982384720.120000

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');*Result:
*982355920.120000

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');*Result: *442800.000000



On Mon, May 29, 2023 at 10:53 PM Don Brown <DBrown@xxxxxxxxxx> wrote:

Thanks Jack,

At the moment I need to format the SQL select to get the required data.

As I said in my OP I am using DBeaver for testing purposes.

But in order to select the required data I need to convert a date into a
long date to select the data.

Then once I extract it I have a 13 length date value I need to convert
into a normal date and time stamp.

So I need a formula to convert from and to.

I tried this but it is not exact

The *Unix epoch* (or *Unix time* or *POSIX time* or *Unix timestamp*) is
the number of seconds that have elapsed since January 1, 1970 (midnight
UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z).
Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but
'epoch' is often used as a synonym for Unix time. Some systems store epoch
dates as a signed 32-bit integer, which might cause problems on January 19,
2038 (known as the Year 2038 problem or Y2038). The converter on this page
converts timestamps in seconds (10-digit), milliseconds (13-digit) and
microseconds (16-digit) to readable dates.
*Human-readable time *
*Seconds*
1 hour 3600 seconds
1 day 86400 seconds
1 week 604800 seconds
1 month (30.44 days) 2629743 seconds
1 year (365.24 days) 31556926 seconds


I get close but not exact so I am missing something.

Thanks

Don





*Don Brown*
Director

*MSD Information Technology*
t: 07 3368 7888
m: 0408 751 644
e: *dbrown@xxxxxxxxxx* <dbrown@xxxxxxxxxx>
*http://www.msd.net.au/ <http://www.msd.net.au/>



From: "Jack Woehr via MIDRANGE-L" <midrange-l@xxxxxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <
midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: "Jack Woehr" <jwoehr@xxxxxxxxxxxxxxxxxxxxxxxx>
Date: 30/05/2023 02:44 PM
Subject: Re: LONGTODATE and DATETOLONG
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>
------------------------------



How are you slurping the data from PostgreSQL? PostgreSQL has functions to
output its data representation as character strings. Surely you can import
them.

https://www.postgresql.org/docs/current/functions-formatting.html

On Mon, May 29, 2023 at 9:57 PM Don Brown via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

We need to process data from a non DB2 source that will be loaded into

DB2

on IBMi

The dates are stored in a long format and my google skills are failing on
finding how to convert.

--

*JACK WOEHR, IBM Champion 2021-2023
<https://www.credly.com/users/jack-woehr/badges>*

*SENIOR IBM i ENGINEER*

303.847.8442
jwoehr@xxxxxxxxxxxxxxxxxxxxxxxx
<https://www.linkedin.com/in/jackwoehr/> stay connected
<https://www.linkedin.com/company/absolute-performance-inc./>

<https://www.absolute-performance.com/>
http://www.absolute-performance.com/

NON-DISCLOSURE NOTICE: This communication including any and all
attachments is for the intended recipient(s) only and may contain

confidential and privileged information. If you are not the intended
recipient of this communication, any disclosure, copying further

distribution or use of this communication is prohibited. If you received
this communication in error, please contact the sender and

delete/destroy all copies of this communication immediately.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.



---

This email has been scanned for computer viruses. Although MSD has taken
reasonable precautions to ensure no viruses are present in this email, MSD
cannot accept responsibility for any loss or damage arising from the use of
this email or attachments. sb

--

*JACK WOEHR, IBM Champion 2021-2023
<https://www.credly.com/users/jack-woehr/badges>*

*SENIOR IBM i ENGINEER*

303.847.8442
jwoehr@xxxxxxxxxxxxxxxxxxxxxxxx
<https://www.linkedin.com/in/jackwoehr/> stay connected
<https://www.linkedin.com/company/absolute-performance-inc./>

<https://www.absolute-performance.com/>
http://www.absolute-performance.com/

NON-DISCLOSURE NOTICE: This communication including any and all
attachments is for the intended recipient(s) only and may contain

confidential and privileged information. If you are not the intended
recipient of this communication, any disclosure, copying further

distribution or use of this communication is prohibited. If you received
this communication in error, please contact the sender and

delete/destroy all copies of this communication immediately.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.