Re: LONGTODATE and DATETOLONG

[Don Brown <DBrown@xxxxxxxxxx>]

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 
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.