Currently I have an app in development that would pull some data in Amazon selling partner api.
My goal is to replicate the reporting from Amazon's seller dashboard. Currently the account is is Eu region using Europe/Berlin timezone. I need to replicate the search with date range.tThe customer for example is using the range 2021-05-01 - 2015-05-11. What I am currently did is create a cron job what would fetch those reports in hourly basis using this this format last day of previous month - last day of current month for me to not to miss any data for the month. My problem is for me to replicate the same result of date range example 2021-05-01 - 2015-05-11 I need to add extraday and 2.5 hours in my sql query.
`SELECT count(id) FROM orders where purchase_date between '2021-05-01' and '2021-05-12 02:18:00'`
The purchase_date from amazon is in isostring format with offset "2021-05-13T15:33:56+00:00" then I save it to mysql using Carbon library Carbon::parse($value['purchase_date'])->format('Y-m-d H:i:s'). The result would be 2021-05-13 15:33:56.
My application's timezone is set to 'UTC'. Any ideas how to achieve the same result without adding 1day and few hours in my sql queries ? My local timezone is Asia/Singapore
Regards
You need to translate what the user means into UTC. When a user from Berlin asks for 2021-05-01 - 2015-05-11 dates, he means:
2021-05-01 00:00:00 - 2015-05-11 23:59:59.999999 Europe/Berlin
Which means for your server:
2021-04-30 22:00:00 - 2015-05-11 21:59:59.999999 UTC
With Carbon you get those with:
$start = Carbon::parse('2021-05-01 Europe/Berlin')->tz('UTC')->format('Y-m-d H:i:s');
$end = Carbon::parse('2015-05-11 Europe/Berlin')->endOfDay()->tz('UTC')->format('Y-m-d H:i:s');