How to query date per fiscal year (time in epoch)-CodePudding

I have an inventory database, on the database I'm storing multiple Items with the date that the item was created. The date is in epoch time.

What I need to do is to create a report per fiscal year - in this case from sept 1, 2020 to Aug 30, 2021, it is possible to do this query if I have the time on epoch or it easier if I store the date on a different format.

id    |    name    |    status    |    time
1     |    pens    |    active    |  1636395754

CodePudding user response：

It's possible using FROM_UNIXTIME function. Example query:

SELECT *,
       FROM_UNIXTIME(time)
FROM mytable
WHERE FROM_UNIXTIME(time) >= '2020-09-01'
  AND FROM_UNIXTIME(time) <= '2021-08-31';

If you want to add a new time column in UTC format based on the existing time column, you can do like this (provided that you have the access privilege):

ALTER TABLE mytable ADD utctime DATETIME;

Then UPDATE the new column:

UPDATE mytable SET utctime=FROM_UNIXTIME(time);

Demo fiddle