-1

Ideally I would like to use SELECT and have the column of unix time show up as dates. Thanks!

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
user3304155
  • 11
  • 1
  • 5
  • 1
    Which SQL? Pretty much anything involving dates is not standardised in SQL. – Amadan Jul 22 '15 at 04:53
  • It cannot be MS SQL on Unix (as indicated by your question tags). Either you're not on Unix, or you're not using MS SQL. – Amadan Jul 22 '15 at 05:02
  • hm sorry I'm pretty new to this, maybe I'm not using the correct terminology. It's basically a timestamp like "1420156800", and I'm using DBvisuliazer, which I think is MS SQL? – user3304155 Jul 22 '15 at 05:15
  • "Unix timestamp" is a data format. "Unix" as a tag indicates your operating system is an Unix (such as Linux, or Mac OS X, and excluding MS Windows). DBVisualizer is not a database, but a database interface, with "support for DB2, Informix, JavaDB/Derby, H2, Mimer SQL, MySQL, NuoDB, Oracle, PostgreSQL, SQL Server, SQLite, Sybase, and, Vertica", running on "Windows, Mac OS X, Linux/UNIX", so that doesn't tell us much... Are you in fact not on Unix, but on Windows, connecting to MS SQL? In DBVisualizer there is connection management, and connection properties should tell you which DB is yours. – Amadan Jul 22 '15 at 05:21
  • ohh I see. sorry for the confusion! I'm using DBvisualizer supporting Vertica, running on Windows. – user3304155 Jul 22 '15 at 05:32
  • While I have no knowledge of Vertica, try [`TO_TIMESTAMP(unixtime)`](https://my.vertica.com/docs/5.0/HTML/Master/9146.htm) – Amadan Jul 22 '15 at 05:39
  • try this URL: http://vertica-forums.com/viewtopic.php?f=63&t=692&p=2276&hilit=unix+timestamp#p2276 Vertica seems to have a **EPOCH** feature that is relevant – Paul Maxwell Jul 22 '15 at 06:14

1 Answers1

0

The way I understood your question is that you already have a column with a Unix timestamp and you wish to format it in a SELECT statement:

dbadmin=> CREATE TABLE tms (unix_time int);
CREATE TABLE

dbadmin=> INSERT INTO tms (unix_time) VALUES (EXTRACT(EPOCH FROM SYSDATE));
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO tms (unix_time) VALUES (EXTRACT(EPOCH FROM SYSDATE));
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO tms (unix_time) VALUES (EXTRACT(EPOCH FROM SYSDATE));
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> SELECT * FROM tms;
 unix_time
------------
 1437575121
 1437575126
 1437575128
(3 rows)

dbadmin=> SELECT TO_TIMESTAMP(unix_time) FROM tms;
    TO_TIMESTAMP
---------------------
 2015-07-22 09:25:21
 2015-07-22 09:25:26
 2015-07-22 09:25:28
(3 rows)
Kermit
  • 33,827
  • 13
  • 85
  • 121