× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. May 2023

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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.