Date data saved from stripe start_date as string timestamp like "1652789095". Now I want to filter with this timestamp string form last 12 months. what should I do ? how can I filter with this timestamp string?
3
-
1this looks like a unix timestamp. Some answers here: stackoverflow.com/questions/16609722/… and here: postgresonline.com/article_pfriendly/3.htmltopsail– topsail2022-06-18 21:29:42 +00:00Commented Jun 18, 2022 at 21:29
-
I would imagine as well that you can subtract 31536000 (the number of seconds in a year) from 1652789095 and that is the number you are looking for as well ( records where start_date is greater than 1621253095) :)topsail– topsail2022-06-18 21:31:00 +00:00Commented Jun 18, 2022 at 21:31
-
Actually date saved in database as string timestamp. Now my goal is filter with this timestamp.Osman Goni Sufy– Osman Goni Sufy2022-06-19 04:28:02 +00:00Commented Jun 19, 2022 at 4:28
Add a comment
|
1 Answer
These are some examples - I'm sure there are plenty of options that would work.
convert to date
select *
from Table
where
to_timestamp(cast(start_date as int)::date > date_add(now(), interval -1 year);
work with unix timestamps
-- approx 1 year ago, by way of example
select *
from Table
where
start_date > '1621253095';
-- exactly one year ago, calculated dynamically
select *
from Table
where
start_date >
cast(unix_timestamp(date_add(now(), interval -1 year)) as varchar);
I'm not a MySQL guy really so forgive any syntax errors and fix up the sql as needed to work in MySQL.
Resources:
