You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by dbutkovic <dr...@gmail.com> on 2020/04/17 09:01:19 UTC
EPOCH seconds in future!
Hi,
Ignite function EXTRACT (EPOCH from CURRENT_TIMESTAMP(3)) return seconds in
future!!!
Current date and time on UNIX host, I am in Zagreb/Croatia CEST GMT+2
[root@incumbossdev01 ~]# date
Fri Apr 17 10:51:10 CEST 2020
Connected to: Apache Ignite (version 2.7.6#20190911-sha1:21f7ca41)
Driver: Apache Ignite Thin JDBC Driver (version
2.7.6#20190911-sha1:21f7ca41)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
sqlline version 1.3.0
0: jdbc:ignite:thin://192.168.50.95/> select CURRENT_TIMESTAMP(3);
+--------------------------------+
| CURRENT_TIMESTAMP(3) |
+--------------------------------+
| 2020-04-17 10:51:17.43 |
+--------------------------------+
1 row selected (0.032 seconds)
https://apacheignite-sql.readme.io/docs/extract
0: jdbc:ignite:thin://192.168.50.95/> select EXTRACT (EPOCH from
CURRENT_TIMESTAMP(3));
+------------------------------------------+
| EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(3)) |
+------------------------------------------+
| 1587120685.619 |
+------------------------------------------+
1 row selected (0.007 seconds)
Convert EPOCH to Timestamp using https://www.epochconverter.com/
The current Unix epoch time is 1587113657
Assuming that this timestamp is in seconds:
GMT: Friday, 17. April 2020 10:51:25.619
Your time zone: petak, 17. travanj 2020 12:51:25.619 GMT+02:00 DST
Relative: In 2 hours
Convert EPOCH to Timestamp using Postgres function
postgres=# select to_timestamp(1587120685.619);
to_timestamp
----------------------------
2020-04-17 12:51:25.619+02
(1 row)
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: EPOCH seconds in future!
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Yes, you are right, according to
https://issues.apache.org/jira/browse/IGNITE-11472 TIMESTAMP WITH TIME ZONE
not supported by Ignite.
I think you will have to work around this quirky behavior. I have left a
comment about this issue specifically.
Regards,
--
Ilya Kasnacheev
пн, 27 апр. 2020 г. в 15:15, dbutkovic <dr...@gmail.com>:
> Hi Ilya,
>
> thnx for replay, I think that Ignite CURRENT_TIMESTAMP() is NOT TIMESTAMP
> WITH TIME ZONE.
>
> Below it test with inserting CURRENT_TIMESTAMP in two tables, one with data
> type TIMESTAMP and second with TIMESTAMP WITH TIME ZONE.
>
> First of all,
> in Ignite documentation I can't find that CURRENT_TIMESTAMP() returns
> TIMESTAMP WITH TIME ZONE.
>
> In H2 documentation we can see that H2 have two data types:
> TIMESTAMP and TIMESTAMP WITH TIME ZONE
> http://www.h2database.com/html/datatypes.html#timestamp_with_time_zone_type
>
> In H2 CURRENT_TIMESTAMP returns the current timestamp with time zone.
>
> I think that in Ignite CURRENT_TIMESTAMP is ONLY CURRENT_TIMESTAMP without
> TIME ZONE.
>
> https://apacheignite-sql.readme.io/docs/current_timestamp
>
>
> 0: jdbc:ignite:thin://192.168.50.95/> SELECT CURRENT_TIMESTAMP();
> +--------------------------------+
> | CURRENT_TIMESTAMP() |
> +--------------------------------+
> | 2020-04-27 13:59:39.814 |
> +--------------------------------+
>
> In first table insert of CURRENT_TIMESTAMP() in data type timestamp is OK.
>
> CREATE TABLE TEST1
> (
> id varchar(10),
> time1 timestamp,
> PRIMARY KEY (id)
> ) WITH "CACHE_NAME=TEST1, DATA_REGION=PersistDataRegion,
> TEMPLATE=REPLICATED, BACKUPS=1";
>
> 0: jdbc:ignite:thin://192.168.50.95/> INSERT INTO TEST1 (id, time1) values
> ('a', CURRENT_TIMESTAMP());
> 1 row affected (0.051 seconds)
>
>
> In second table insert of CURRENT_TIMESTAMP() in data type timestamp with
> time zone is NOT OK.
>
> CREATE TABLE TEST2
> (
> id varchar(10),
> time1 timestamp with time zone,
> PRIMARY KEY (id)
> ) WITH "CACHE_NAME
>
>
> 0: jdbc:ignite:thin://192.168.50.95/> INSERT INTO TEST2 (id, time1) values
> ('a', CURRENT_TIMESTAMP());
> Error: class org.apache.ignite.IgniteException: Failed to execute SQL
> query.
> Hexadecimal string with odd number of characters: "2020-04-27
> 13:59:00.599";
> SQL statement:
> SELECT
> TABLE.ID,
> TABLE.TIME1
> FROM TABLE(ID VARCHAR(10)=('a',), TIME1 OTHER=(CURRENT_TIMESTAMP(),))
> [90003-197] (state=50000,code=1)
> java.sql.SQLException: class org.apache.ignite.IgniteException: Failed to
> execute SQL query. Hexadecimal string with odd number of characters:
> "2020-04-27 13:59:00.599"; SQL statement:
> SELECT
> TABLE.ID,
> TABLE.TIME1
> FROM TABLE(ID VARCHAR(10)=('a',), TIME1 OTHER=(CURRENT_TIMESTAMP(),))
> [90003-197]
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
>
>
> Please, do you know why I can't insert CURRENT_TIMESTAMP() in data type
> timestamp with time zone.
>
>
> Best regards
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: EPOCH seconds in future!
Posted by dbutkovic <dr...@gmail.com>.
Hi Ilya,
thnx for replay, I think that Ignite CURRENT_TIMESTAMP() is NOT TIMESTAMP
WITH TIME ZONE.
Below it test with inserting CURRENT_TIMESTAMP in two tables, one with data
type TIMESTAMP and second with TIMESTAMP WITH TIME ZONE.
First of all,
in Ignite documentation I can't find that CURRENT_TIMESTAMP() returns
TIMESTAMP WITH TIME ZONE.
In H2 documentation we can see that H2 have two data types:
TIMESTAMP and TIMESTAMP WITH TIME ZONE
http://www.h2database.com/html/datatypes.html#timestamp_with_time_zone_type
In H2 CURRENT_TIMESTAMP returns the current timestamp with time zone.
I think that in Ignite CURRENT_TIMESTAMP is ONLY CURRENT_TIMESTAMP without
TIME ZONE.
https://apacheignite-sql.readme.io/docs/current_timestamp
0: jdbc:ignite:thin://192.168.50.95/> SELECT CURRENT_TIMESTAMP();
+--------------------------------+
| CURRENT_TIMESTAMP() |
+--------------------------------+
| 2020-04-27 13:59:39.814 |
+--------------------------------+
In first table insert of CURRENT_TIMESTAMP() in data type timestamp is OK.
CREATE TABLE TEST1
(
id varchar(10),
time1 timestamp,
PRIMARY KEY (id)
) WITH "CACHE_NAME=TEST1, DATA_REGION=PersistDataRegion,
TEMPLATE=REPLICATED, BACKUPS=1";
0: jdbc:ignite:thin://192.168.50.95/> INSERT INTO TEST1 (id, time1) values
('a', CURRENT_TIMESTAMP());
1 row affected (0.051 seconds)
In second table insert of CURRENT_TIMESTAMP() in data type timestamp with
time zone is NOT OK.
CREATE TABLE TEST2
(
id varchar(10),
time1 timestamp with time zone,
PRIMARY KEY (id)
) WITH "CACHE_NAME
0: jdbc:ignite:thin://192.168.50.95/> INSERT INTO TEST2 (id, time1) values
('a', CURRENT_TIMESTAMP());
Error: class org.apache.ignite.IgniteException: Failed to execute SQL query.
Hexadecimal string with odd number of characters: "2020-04-27 13:59:00.599";
SQL statement:
SELECT
TABLE.ID,
TABLE.TIME1
FROM TABLE(ID VARCHAR(10)=('a',), TIME1 OTHER=(CURRENT_TIMESTAMP(),))
[90003-197] (state=50000,code=1)
java.sql.SQLException: class org.apache.ignite.IgniteException: Failed to
execute SQL query. Hexadecimal string with odd number of characters:
"2020-04-27 13:59:00.599"; SQL statement:
SELECT
TABLE.ID,
TABLE.TIME1
FROM TABLE(ID VARCHAR(10)=('a',), TIME1 OTHER=(CURRENT_TIMESTAMP(),))
[90003-197]
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
Please, do you know why I can't insert CURRENT_TIMESTAMP() in data type
timestamp with time zone.
Best regards
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: EPOCH seconds in future!
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
In Ignite, CURRENT_TIMESTAMP() is TIMESTAMP WITH TIME ZONE.
Regards,
--
Ilya Kasnacheev
вс, 26 апр. 2020 г. в 14:51, dbutkovic <dr...@gmail.com>:
> Hi Ilya,
>
> thanks a lot for the reply,
> it is surprising that EPOCH is not always the same regardless of the
> timezone.
> I did a test on two Ignite instances, one on a host with timezone 'Europe /
> Zagreb' and the other on UTC.
> The EPOCH obtained in bash is the same on both hosts and the EPOCH obtained
> in Ignite sql is not the same.
>
> When I select CURRENT_TIMESTAMP() Ignite does not return timezone
> information, how do distinct if returned timestamp is local time or UTC?
>
> 0: jdbc:ignite:thin://192.168.50.95/> SELECT CURRENT_TIMESTAMP(),
> FORMATDATETIME( CURRENT_TIMESTAMP(), 'yyyy-MM-dd HH:mm:ss z', 'en',
> 'Europe/Zagreb');
>
> +--------------------------------+--------------------------------------------------------------------------------+
> | CURRENT_TIMESTAMP() | FORMATDATETIME(CURRENT_TIMESTAMP(),
> 'yyyy-MM-dd HH:mm:ss z', 'en', 'Europe/Zag |
>
> +--------------------------------+--------------------------------------------------------------------------------+
> | 2020-04-26 13:49:36.413 | 2020-04-26 13:49:36 CEST
>
> |
>
> +--------------------------------+--------------------------------------------------------------------------------+
> 1 row selected (0.002 seconds)
>
>
>
> <
> http://apache-ignite-users.70518.x6.nabble.com/file/t2557/epoch_Ignite.png>
>
>
> Best regards
> Dren
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: EPOCH seconds in future!
Posted by dbutkovic <dr...@gmail.com>.
Hi Ilya,
thanks a lot for the reply,
it is surprising that EPOCH is not always the same regardless of the
timezone.
I did a test on two Ignite instances, one on a host with timezone 'Europe /
Zagreb' and the other on UTC.
The EPOCH obtained in bash is the same on both hosts and the EPOCH obtained
in Ignite sql is not the same.
When I select CURRENT_TIMESTAMP() Ignite does not return timezone
information, how do distinct if returned timestamp is local time or UTC?
0: jdbc:ignite:thin://192.168.50.95/> SELECT CURRENT_TIMESTAMP(),
FORMATDATETIME( CURRENT_TIMESTAMP(), 'yyyy-MM-dd HH:mm:ss z', 'en',
'Europe/Zagreb');
+--------------------------------+--------------------------------------------------------------------------------+
| CURRENT_TIMESTAMP() | FORMATDATETIME(CURRENT_TIMESTAMP(),
'yyyy-MM-dd HH:mm:ss z', 'en', 'Europe/Zag |
+--------------------------------+--------------------------------------------------------------------------------+
| 2020-04-26 13:49:36.413 | 2020-04-26 13:49:36 CEST
|
+--------------------------------+--------------------------------------------------------------------------------+
1 row selected (0.002 seconds)
<http://apache-ignite-users.70518.x6.nabble.com/file/t2557/epoch_Ignite.png>
Best regards
Dren
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: EPOCH seconds in future!
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello again!
I guess that EPOCH is calculated relative to current time zone, not UTC.
This means you will have to adjust it:
~/Downloads/apache-ignite-2.8.0-bin% date
Пт апр 24 18:16:55 MSK 2020
~/Downloads/apache-ignite-2.8.0-bin% bin/sqlline.sh
sqlline version 1.3.0
sqlline> !connect jdbc:ignite:thin://localhost
0: jdbc:ignite:thin://localhost> select EXTRACT (EPOCH from
CURRENT_TIMESTAMP(3));
+------------------------------------------+
| EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(3)) |
+------------------------------------------+
| 1587752229.496 |
+------------------------------------------+
1 row selected (0,041 seconds)
0: jdbc:ignite:thin://localhost> select dateadd('s', 1587752229,
'1970-01-01'); -- Please note that's LOCAL TIME ZONE 1970-01-01, not UTC
+---------------------------------+
| TIMESTAMP '2020-04-24 18:17:09' |
+---------------------------------+
| 2020-04-24 18:17:09.0 |
+---------------------------------+
1 row selected (0,006 seconds)
As you can see, you can round-trip such EPOCH values.
Regards,
--
Ilya Kasnacheev
пт, 24 апр. 2020 г. в 18:10, Ilya Kasnacheev <il...@gmail.com>:
> Hello!
>
> I guess that EPOCH() returns
> --
> Ilya Kasnacheev
>
>
> пт, 17 апр. 2020 г. в 12:07, dbutkovic <dr...@gmail.com>:
>
>> Hi,
>> Ignite function EXTRACT (EPOCH from CURRENT_TIMESTAMP(3)) return seconds
>> in
>> future!!!
>>
>>
>> Current date and time on UNIX host, I am in Zagreb/Croatia CEST GMT+2
>>
>> [root@incumbossdev01 ~]# date
>> Fri Apr 17 10:51:10 CEST 2020
>>
>>
>>
>> Connected to: Apache Ignite (version 2.7.6#20190911-sha1:21f7ca41)
>> Driver: Apache Ignite Thin JDBC Driver (version
>> 2.7.6#20190911-sha1:21f7ca41)
>> Autocommit status: true
>> Transaction isolation: TRANSACTION_REPEATABLE_READ
>> sqlline version 1.3.0
>> 0: jdbc:ignite:thin://192.168.50.95/> select CURRENT_TIMESTAMP(3);
>> +--------------------------------+
>> | CURRENT_TIMESTAMP(3) |
>> +--------------------------------+
>> | 2020-04-17 10:51:17.43 |
>> +--------------------------------+
>> 1 row selected (0.032 seconds)
>>
>>
>> https://apacheignite-sql.readme.io/docs/extract
>>
>> 0: jdbc:ignite:thin://192.168.50.95/> select EXTRACT (EPOCH from
>> CURRENT_TIMESTAMP(3));
>> +------------------------------------------+
>> | EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(3)) |
>> +------------------------------------------+
>> | 1587120685.619 |
>> +------------------------------------------+
>> 1 row selected (0.007 seconds)
>>
>>
>> Convert EPOCH to Timestamp using https://www.epochconverter.com/
>>
>> The current Unix epoch time is 1587113657
>>
>> Assuming that this timestamp is in seconds:
>> GMT: Friday, 17. April 2020 10:51:25.619
>> Your time zone: petak, 17. travanj 2020 12:51:25.619 GMT+02:00 DST
>> Relative: In 2 hours
>>
>>
>> Convert EPOCH to Timestamp using Postgres function
>>
>> postgres=# select to_timestamp(1587120685.619);
>> to_timestamp
>> ----------------------------
>> 2020-04-17 12:51:25.619+02
>> (1 row)
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>
Re: EPOCH seconds in future!
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I guess that EPOCH() returns
--
Ilya Kasnacheev
пт, 17 апр. 2020 г. в 12:07, dbutkovic <dr...@gmail.com>:
> Hi,
> Ignite function EXTRACT (EPOCH from CURRENT_TIMESTAMP(3)) return seconds in
> future!!!
>
>
> Current date and time on UNIX host, I am in Zagreb/Croatia CEST GMT+2
>
> [root@incumbossdev01 ~]# date
> Fri Apr 17 10:51:10 CEST 2020
>
>
>
> Connected to: Apache Ignite (version 2.7.6#20190911-sha1:21f7ca41)
> Driver: Apache Ignite Thin JDBC Driver (version
> 2.7.6#20190911-sha1:21f7ca41)
> Autocommit status: true
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> sqlline version 1.3.0
> 0: jdbc:ignite:thin://192.168.50.95/> select CURRENT_TIMESTAMP(3);
> +--------------------------------+
> | CURRENT_TIMESTAMP(3) |
> +--------------------------------+
> | 2020-04-17 10:51:17.43 |
> +--------------------------------+
> 1 row selected (0.032 seconds)
>
>
> https://apacheignite-sql.readme.io/docs/extract
>
> 0: jdbc:ignite:thin://192.168.50.95/> select EXTRACT (EPOCH from
> CURRENT_TIMESTAMP(3));
> +------------------------------------------+
> | EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(3)) |
> +------------------------------------------+
> | 1587120685.619 |
> +------------------------------------------+
> 1 row selected (0.007 seconds)
>
>
> Convert EPOCH to Timestamp using https://www.epochconverter.com/
>
> The current Unix epoch time is 1587113657
>
> Assuming that this timestamp is in seconds:
> GMT: Friday, 17. April 2020 10:51:25.619
> Your time zone: petak, 17. travanj 2020 12:51:25.619 GMT+02:00 DST
> Relative: In 2 hours
>
>
> Convert EPOCH to Timestamp using Postgres function
>
> postgres=# select to_timestamp(1587120685.619);
> to_timestamp
> ----------------------------
> 2020-04-17 12:51:25.619+02
> (1 row)
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>