Hi All,

It appears that the gmt_timestamp function (amongst others) can only handletimes after 1/1/1970. Furthermore, it can only take an integer4 input which makes the maximum date it can return to be 2038_01_19 03:14:07 GMT.

The attempt to use a integer8 input to gmt_timestamp fails:

select gmt_timestamp(int8(2147483648))\g
E_SC0206 An internal error prevents further processing of this query.
Associated error messages which provide more detailed information about
the problem can be found in the error log, II_CONFIG:errlog.log
And in the errlog we see:
BRAT_CTSU_OX_AC_UK::[52992 , 17100 , 00002aaacc7f6200, adustring.c:3670 ]: Tue Jul 19 11:18:55 2011 E_AD9998_INTERNAL_ERROR AnADF internal coding error has been detected (cvgmt value overflow).
BRAT_CTSU_OX_AC_UK::[52992 , 17100 , 00002aaacc7f6200, scsqncr.c:13891 ]: Tue Jul 19 11:18:55 2011 E_SC0216_QEF_ERROR Error returned by QEF.
BRAT_CTSU_OX_AC_UK::[52992 , 17100 , 00002aaacc7f6200, scsqncr.c:13892 ]: Tue Jul 19 11:18:55 2011 E_SC0206_CANNOT_PROCESS Aninternal error prevents further processing of this query.
Associated error messages which provide more detailed information about theproblem can be found in the error log, II_CONFIG:errlog.log
BRAT_CTSU_OX_AC_UK::[52992 , 17100 , 00002aaacc7f6200, scsqncr.c:13892 ]: PQuery: select gmt_timestamp(int8(2147483648))
BRAT_CTSU_OX_AC_UK::[52992 , 17100 , 00002aaacc7f6200, scsqncr.c:13892 ]: Query: select gmt_timestamp(int8(2147483648)) ^@
BRAT_CTSU_OX_AC_UK::[52992 , 17100 , 00002aaacc7f6200, scsqncr.c:13892 ]: LQuery: set AUTOCOMMIT OFF^@

Also any negative input is treated (silently) as zero....
select gmt_timestamp(-1000)\g
Will return 1970_01_01 00:00:00 GMT

I need a function which can take int8 items both positive and negative and generate the appropriate date. Does anyone know of such a C function?

Marty

Martin Bowes wrote:

This is a well-known problem with a name:
http://en.wikipedia.org/wiki/Year_2038_problem

[snip]

Strangely, I think there is very little agreement and hence widely
adopted solution for this. I shall be interested to hear different.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

How about this:


Code:
--------------------
1> select date_gmt(date('01-jan-1970 00:00:00 GMT')+
date(varchar(int8(2147483648)/3600)+' hrs '+
varchar(mod(int8(2147483648),3600))+ ' sec'))



┌───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”�
│col1 │
├───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”¤
│2038_01_19 03:14:08 GMT │
└───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”˜

2> select date_gmt(date('01-jan-1970 00:00:00 GMT')+
date(varchar(int8(-2147483648)/3600)+' hrs '+
varchar(mod(int8(-2147483648),3600))+ ' sec'))



┌───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”�
│col1 │
├───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”¤
│1901_12_13 20:45:52 GMT │
└───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”˜
(1 row)
--------------------


--
Bodo

Hi Bodo,

Many thanks for that...seems to work like a dream!

Marty

-----Original Message-----
From: Ingres Forums [mailto:info-ingres (AT) kettleriverconsulting (DOT) com]
Sent: 19 July 2011 12:42
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] gmt_timestamp() and dates beyond 2038, before 1/1/1970


How about this:


Code:
--------------------
1> select date_gmt(date('01-jan-1970 00:00:00 GMT')+
date(varchar(int8(2147483648)/3600)+' hrs '+
varchar(mod(int8(2147483648),3600))+ ' sec'))



┌───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”�
│col1 │
├───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”¤
│2038_01_19 03:14:08 GMT │
└───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”˜

2> select date_gmt(date('01-jan-1970 00:00:00 GMT')+
date(varchar(int8(-2147483648)/3600)+' hrs '+
varchar(mod(int8(-2147483648),3600))+ ' sec'))



┌───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”�
│col1 │
├───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”¤
│1901_12_13 20:45:52 GMT │
└───────────────┠€â”€â”€â”€â”€â”€â”€â”€â”€â”€â”˜
(1 row)
--------------------


--
Bodo

Martin Bowes;38284 Wrote:
It would be a dream if get_timestamp() wouldn't choke on it.
"internal coding error has been detected" says it all.
You should open a ServiceDesk issue for it.


--
Bodo

Hi Bodo,

Never fear! I raised it as a service desk query at the same time I put the original post on info_ingres.

Marty

-----Original Message-----
From: Ingres Forums [mailto:info-ingres (AT) kettleriverconsulting (DOT) com]
Sent: 20 July 2011 11:11
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] gmt_timestamp() and dates beyond 2038, before 1/1/1970


Martin Bowes;38284 Wrote:
It would be a dream if get_timestamp() wouldn't choke on it.
"internal coding error has been detected" says it all.
You should open a ServiceDesk issue for it.


--
Bodo


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres