I have a table as DB. The table has three columns and multiple rows. I want to get a the rows which their date is between 2019-2020. However, the date column is integer and I try to use the following code to read it, but I got an error:

pd.read_sql("select table.*, date_format(from_unixtime(table.time), '%Y-%m-%d') as
date where date between 'the period I want' from table",db)

Here is the table in my database.

Could you please help me with that? Thanks

CodePudding user response：

from_unixtime() doesn't return a string... it returns a timestamp that can be compared like any other timestamp.

Schema (MySQL v8.0)

CREATE TABLE le_tbl (
  `id` INTEGER,
  `time` INTEGER,
  `val` INTEGER
);

INSERT INTO le_tbl
  (`id`, `time`, `val`)
VALUES
  ('1', '1554012000', '5'),
  ('2', '1554012900', '6'),
  ('3', '1554013800', '7');

Query

SELECT id
     , from_unixtime(time) time_col
     , val
FROM le_tbl
WHERE from_unixtime(time) BETWEEN '2019-01-01' AND '2021-01-01';

View on DB Fiddle