It does not matter which timezone you store timestamps in, though most systems use UTC/GMT), as long as you make the appropriate adjustments to ensure your Trade and Value dates are correct, remembering New York, UK and New Zealand move clocks on different days and directions.
More specifically: by convention, the institutional FX market operates continuously from 05.00 Sydney time on Mondays until 17.00 New York time Fridays - these are local times, e.g. NY is Eastern Daylight Time (EDT) in the NY summer and Eastern Standard Time (EST) in the NY winter (see the ACI model code https://acifma.com/model-code).
Within that week, trades conducted on Sunday carry a Trade Date of Monday; thereafter the Trade Date changes at 17.00 New York time on each of Monday through Friday. So a trade conducted at 16.00 New York time Monday is considered a Monday trade; a trade two hours later at 18.00 is considered a Tuesday trade. Again these are Eastern Daylight Time in the summer and Eastern Standard Time in the winter. Value Dates (mostly two days after Trade Date, CAD being one day) work the same way.
There is a single exception to that rule. New Zealand Dollar (NZD) and all its crosses change Trade and Value date at 07.00 New Zealand Time (NZDT in NZ summer, NZST in NZ winter).
Hope this helps and let me know if anything is unclear!