0

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?

Osman Goni Sufy
  • 21
  • 3
  • 1
    this looks like a unix timestamp. Some answers here: https://stackoverflow.com/questions/16609722/postgresql-how-to-convert-from-unix-epoch-to-date and here: https://www.postgresonline.com/article_pfriendly/3.html – topsail Jun 18 '22 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 Jun 18 '22 at 21:31
  • Actually date saved in database as string timestamp. Now my goal is filter with this timestamp. – Osman Goni Sufy Jun 19 '22 at 04:28

1 Answers1

0

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:

PostgreSQL: how to convert from Unix epoch to date?

https://www.postgresonline.com/article_pfriendly/3.html

topsail
  • 2,186
  • 3
  • 17
  • 17