MySQL unix_timestamp() equivalent?
Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.
Hello, There might be a way of constructing a timestamp in MonetDB from an epoch, but I can not think of one. Regarding the reference that you gave to unix_timestamp (generating date or times from strings): we have something similar in MonetDB, some examples: SELECT str_to_date('23-09-1987', '%d-%m-%Y') AS "date", str_to_time('11:40', '%H:%M') AS "time", str_to_timestamp('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "timestamp"; +------------+----------+----------------------------+ | date | time | timestamp | +============+==========+============================+ | 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 | +------------+----------+----------------------------+ On 28-02-16 07:28, Vijay Krishna wrote:
Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Yes, I have come across this solution, Robin. But my problem involves *converting epoch (a bigint value) into Date or Timestamp*. I went through the Temporal functions page and a few questions in the user thread in the past, but am unable to find a solution till now. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305. On Mon, Feb 29, 2016 at 11:43 AM, Robin Cijvat < robin.cijvat@monetdbsolutions.com> wrote:
Hello,
There might be a way of constructing a timestamp in MonetDB from an epoch, but I can not think of one. Regarding the reference that you gave to unix_timestamp (generating date or times from strings): we have something similar in MonetDB, some examples:
SELECT str_to_date('23-09-1987', '%d-%m-%Y') AS "date", str_to_time('11:40', '%H:%M') AS "time", str_to_timestamp('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "timestamp";
+------------+----------+----------------------------+ | date | time | timestamp | +============+==========+============================+ | 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 | +------------+----------+----------------------------+
On 28-02-16 07:28, Vijay Krishna wrote:
Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.
_______________________________________________ users-list mailing listusers-list@monetdb.orghttps://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hey Vijay,
The function sys.epoch converts an epoch value to a timestamp.
sql>SELECT epoch(1456741924);
+----------------------------+
| epoch_single_value |
+============================+
| 2016-02-29 10:32:04.000000 |
+----------------------------+
1 tuple (1.184ms)
Hope that helps,
Mark
On Mon, Feb 29, 2016 at 11:27 AM, Vijay Krishna
Yes, I have come across this solution, Robin.
But my problem involves *converting epoch (a bigint value) into Date or Timestamp*.
I went through the Temporal functions page and a few questions in the user thread in the past, but am unable to find a solution till now.
Any help much appreciated.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
On Mon, Feb 29, 2016 at 11:43 AM, Robin Cijvat < robin.cijvat@monetdbsolutions.com> wrote:
Hello,
There might be a way of constructing a timestamp in MonetDB from an epoch, but I can not think of one. Regarding the reference that you gave to unix_timestamp (generating date or times from strings): we have something similar in MonetDB, some examples:
SELECT str_to_date('23-09-1987', '%d-%m-%Y') AS "date", str_to_time('11:40', '%H:%M') AS "time", str_to_timestamp('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "timestamp";
+------------+----------+----------------------------+ | date | time | timestamp | +============+==========+============================+ | 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 | +------------+----------+----------------------------+
On 28-02-16 07:28, Vijay Krishna wrote:
Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.
_______________________________________________ users-list mailing listusers-list@monetdb.orghttps://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Mark,
Thanks for the prompt reply. But it seems not working in my installation. I
am using Jan2014 release. Is this function available in that bundle?
I got this message when I tried executing the above example.
$ ./bin/mclient -d testdb
Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014)
Database: MonetDB v11.17.9 (Jan2014),
'mapi:monetdb://vijay-1833:50000/crmdb'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>SELECT epoch(1456741924);
SELECT: no such unary operator 'epoch(int)'
Please help me with what I'm missing here. Thanks a ton in advance.
Thanks & Regards,
Vijayakrishna.P.
Mobile : (+91) 9500402305.
On Mon, Feb 29, 2016 at 4:02 PM, Mark Raasveldt
Hey Vijay,
The function sys.epoch converts an epoch value to a timestamp.
sql>SELECT epoch(1456741924); +----------------------------+ | epoch_single_value | +============================+ | 2016-02-29 10:32:04.000000 | +----------------------------+ 1 tuple (1.184ms)
Hope that helps,
Mark
On Mon, Feb 29, 2016 at 11:27 AM, Vijay Krishna
wrote:
Yes, I have come across this solution, Robin.
But my problem involves *converting epoch (a bigint value) into Date or Timestamp*.
I went through the Temporal functions page and a few questions in the user thread in the past, but am unable to find a solution till now.
Any help much appreciated.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
On Mon, Feb 29, 2016 at 11:43 AM, Robin Cijvat < robin.cijvat@monetdbsolutions.com> wrote:
Hello,
There might be a way of constructing a timestamp in MonetDB from an epoch, but I can not think of one. Regarding the reference that you gave to unix_timestamp (generating date or times from strings): we have something similar in MonetDB, some examples:
SELECT str_to_date('23-09-1987', '%d-%m-%Y') AS "date", str_to_time('11:40', '%H:%M') AS "time", str_to_timestamp('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "timestamp";
+------------+----------+----------------------------+ | date | time | timestamp | +============+==========+============================+ | 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 | +------------+----------+----------------------------+
On 28-02-16 07:28, Vijay Krishna wrote:
Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.
_______________________________________________ users-list mailing listusers-list@monetdb.orghttps://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
try SELECT sys.epoch(1456741924); ^^^^ Stefan ----- On Feb 29, 2016, at 6:27 PM, Vijay Krishna vijayakrishna55@gmail.com wrote:
Hi Mark,
Thanks for the prompt reply. But it seems not working in my installation. I am using Jan2014 release. Is this function available in that bundle?
I got this message when I tried executing the above example.
$ ./bin/mclient -d testdb Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014) Database: MonetDB v11.17.9 (Jan2014), 'mapi:monetdb://vijay-1833:50000/crmdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT epoch(1456741924); SELECT: no such unary operator 'epoch(int)'
Please help me with what I'm missing here. Thanks a ton in advance.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
On Mon, Feb 29, 2016 at 4:02 PM, Mark Raasveldt < mark.raasveldt@gmail.com > wrote:
Hey Vijay,
The function sys.epoch converts an epoch value to a timestamp.
sql>SELECT epoch(1456741924); +----------------------------+ | epoch_single_value | +============================+ | 2016-02-29 10:32:04.000000 | +----------------------------+ 1 tuple (1.184ms)
Hope that helps,
Mark
On Mon, Feb 29, 2016 at 11:27 AM, Vijay Krishna < vijayakrishna55@gmail.com > wrote:
Yes, I have come across this solution, Robin.
But my problem involves converting epoch (a bigint value) into Date or Timestamp .
I went through the Temporal functions page and a few questions in the user thread in the past, but am unable to find a solution till now.
Any help much appreciated.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305 .
On Mon, Feb 29, 2016 at 11:43 AM, Robin Cijvat < robin.cijvat@monetdbsolutions.com > wrote:
Hello,
There might be a way of constructing a timestamp in MonetDB from an epoch, but I can not think of one. Regarding the reference that you gave to unix_timestamp (generating date or times from strings): we have something similar in MonetDB, some examples:
SELECT str_to_date('23-09-1987', '%d-%m-%Y') AS "date", str_to_time('11:40', '%H:%M') AS "time", str_to_timestamp('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "timestamp";
+------------+----------+----------------------------+ | date | time | timestamp | +============+==========+============================+ | 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 | +------------+----------+----------------------------+ On 28-02-16 07:28, Vijay Krishna wrote:
Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305 . _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
Hi Stefan,
sys.epoch also gives me the same result. Seems I'm clearing missing
something. Is there any dependent packages for this to run (with Jan2014
bundle on Mac)?
$ ./bin/mclient -d crmdb
Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014)
Database: MonetDB v11.17.9 (Jan2014),
'mapi:monetdb://vijay-1833:50000/crmdb'
Type \q to quit, \? for a list of available commands
auto commit mode: on
*sql>SELECT sys.epoch(1456741924);*
*SELECT: no such unary operator 'epoch(int)'*
sql>
Thanks & Regards,
Vijayakrishna.P.
Mobile : (+91) 9500402305.
On Mon, Feb 29, 2016 at 11:52 PM, Stefan Manegold
try SELECT sys.epoch(1456741924); ^^^^
Stefan
----- On Feb 29, 2016, at 6:27 PM, Vijay Krishna vijayakrishna55@gmail.com wrote:
Hi Mark,
Thanks for the prompt reply. But it seems not working in my installation. I am using Jan2014 release. Is this function available in that bundle?
I got this message when I tried executing the above example.
$ ./bin/mclient -d testdb Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014) Database: MonetDB v11.17.9 (Jan2014), 'mapi:monetdb://vijay-1833:50000/crmdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT epoch(1456741924); SELECT: no such unary operator 'epoch(int)'
Please help me with what I'm missing here. Thanks a ton in advance.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
On Mon, Feb 29, 2016 at 4:02 PM, Mark Raasveldt < mark.raasveldt@gmail.com > wrote:
Hey Vijay,
The function sys.epoch converts an epoch value to a timestamp.
sql>SELECT epoch(1456741924); +----------------------------+ | epoch_single_value | +============================+ | 2016-02-29 10:32:04.000000 | +----------------------------+ 1 tuple (1.184ms)
Hope that helps,
Mark
On Mon, Feb 29, 2016 at 11:27 AM, Vijay Krishna < vijayakrishna55@gmail.com > wrote:
Yes, I have come across this solution, Robin.
But my problem involves converting epoch (a bigint value) into Date or Timestamp .
I went through the Temporal functions page and a few questions in the user thread in the past, but am unable to find a solution till now.
Any help much appreciated.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305 .
On Mon, Feb 29, 2016 at 11:43 AM, Robin Cijvat < robin.cijvat@monetdbsolutions.com > wrote:
Hello,
There might be a way of constructing a timestamp in MonetDB from an epoch, but I can not think of one. Regarding the reference that you gave to unix_timestamp (generating date or times from strings): we have something similar in MonetDB, some examples:
SELECT str_to_date('23-09-1987', '%d-%m-%Y') AS "date", str_to_time('11:40', '%H:%M') AS "time", str_to_timestamp('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "timestamp";
+------------+----------+----------------------------+ | date | time | timestamp | +============+==========+============================+ | 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 | +------------+----------+----------------------------+ On 28-02-16 07:28, Vijay Krishna wrote:
Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305 . _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Maybe sys.epoch() got introduced only after Jan2014 ? (I cannot verify this right now ...) Try select * from sys.functions where name = 'epoch'; to check. Please also consider upgrading to a newer (better: the latest) version of MonetDB (currently Jul2015-SP2), BUT (as always) before doing so, first (back-up and) dump your database(s) that is/are dear to you, AND then first upgrade any pre Oct2014-SP4 database to Oct2014-SP4 before then upgrading that one to Jul2015-SP2 (or start with a fresh empty Jul2015-SP2 database and restore your pre Oct2014-SP4 dump into it). Stefan ----- On Mar 2, 2016, at 7:10 AM, Vijay Krishna vijayakrishna55@gmail.com wrote:
Hi Stefan,
sys.epoch also gives me the same result. Seems I'm clearing missing something. Is there any dependent packages for this to run (with Jan2014 bundle on Mac)?
$ ./bin/mclient -d crmdb Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014) Database: MonetDB v11.17.9 (Jan2014), 'mapi:monetdb://vijay-1833:50000/crmdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT sys.epoch(1456741924); SELECT: no such unary operator 'epoch(int)' sql>
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
On Mon, Feb 29, 2016 at 11:52 PM, Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:
try SELECT sys.epoch(1456741924); ^^^^
Stefan
----- On Feb 29, 2016, at 6:27 PM, Vijay Krishna vijayakrishna55@gmail.com wrote:
Hi Mark,
Thanks for the prompt reply. But it seems not working in my installation. I am using Jan2014 release. Is this function available in that bundle?
I got this message when I tried executing the above example.
$ ./bin/mclient -d testdb Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014) Database: MonetDB v11.17.9 (Jan2014), 'mapi:monetdb://vijay-1833:50000/crmdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT epoch(1456741924); SELECT: no such unary operator 'epoch(int)'
Please help me with what I'm missing here. Thanks a ton in advance.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
On Mon, Feb 29, 2016 at 4:02 PM, Mark Raasveldt < mark.raasveldt@gmail.com > wrote:
Hey Vijay,
The function sys.epoch converts an epoch value to a timestamp.
sql>SELECT epoch(1456741924); +----------------------------+ | epoch_single_value | +============================+ | 2016-02-29 10:32:04.000000 | +----------------------------+ 1 tuple (1.184ms)
Hope that helps,
Mark
On Mon, Feb 29, 2016 at 11:27 AM, Vijay Krishna < vijayakrishna55@gmail.com > wrote:
Yes, I have come across this solution, Robin.
But my problem involves converting epoch (a bigint value) into Date or Timestamp .
I went through the Temporal functions page and a few questions in the user thread in the past, but am unable to find a solution till now.
Any help much appreciated.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305 .
On Mon, Feb 29, 2016 at 11:43 AM, Robin Cijvat < robin.cijvat@monetdbsolutions.com > wrote:
Hello,
There might be a way of constructing a timestamp in MonetDB from an epoch, but I can not think of one. Regarding the reference that you gave to unix_timestamp (generating date or times from strings): we have something similar in MonetDB, some examples:
SELECT str_to_date('23-09-1987', '%d-%m-%Y') AS "date", str_to_time('11:40', '%H:%M') AS "time", str_to_timestamp('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "timestamp";
+------------+----------+----------------------------+ | date | time | timestamp | +============+==========+============================+ | 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 | +------------+----------+----------------------------+ On 28-02-16 07:28, Vijay Krishna wrote:
Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305 . _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
participants (4)
-
Mark Raasveldt
-
Robin Cijvat
-
Stefan Manegold
-
Vijay Krishna