Handling Subscription Dates

[SaranacLake]

I could use some advice on how to best handle subscription dates in MySQL.

This may seem like an easy thing, but working with dates and time and changes across days and years and timezones can actually be rather complex!

Let's say my table looks like this...

SUBSCRIPTION

- id

- member_id

- plan_id

- start_date

- end_date

 

And let's assume that subscription are for 1 year.

If a user subscribes on 2020-04-17 @ 3:00pm local time, then how should I handle the "end_date"?

1.) Would it be easier to just work with Dates and avoid Time?

 

2.) If so, does a Subscription have a start_date of "2020-04-17" and an end_date of "2020-04-16" or an end_date of "2020-04-17"?

 

3.) How do I handle - I think it's called "localization" - where the client time and server time are different?  For example, if a user is in London and subscribes on 2020-04-17 @ 4:00am UTC but my server is in the U.S. and has a time of 2020-04-016 @ 11:00pm, do I make the user wait an hour before their subscription kicks in because my database is on Eastern Time?

 

Typically I am for "precision", but I also don't ant to make dealing with Dates and Times so difficult that it creates a real hassle for me from a design and coding standpoint.

 

Oh, I guess my questions apply to both PHP and MySQL!  🙂

 

 

[requinix]

1 hour ago, SaranacLake said:

1.) Would it be easier to just work with Dates and avoid Time?

Unless you want to penny-pinch your users out of individual hours, dates are easier.

1 hour ago, SaranacLake said:

2.) If so, does a Subscription have a start_date of "2020-04-17" and an end_date of "2020-04-16" or an end_date of "2020-04-17"?

Which one makes more sense to you? Decide now. Don't ask. Decide.

1 hour ago, SaranacLake said:

3.) How do I handle - I think it's called "localization" - where the client time and server time are different?  For example, if a user is in London and subscribes on 2020-04-17 @ 4:00am UTC but my server is in the U.S. and has a time of 2020-04-016 @ 11:00pm, do I make the user wait an hour before their subscription kicks in because my database is on Eastern Time?

Here's a question: does it actually matter? Think about it.

[SaranacLake]

11 minutes ago, requinix said:

Unless you want to penny-pinch your users out of individual hours, dates are easier.

No, I do not.

 

11 minutes ago, requinix said:

Which one makes more sense to you? Decide now. Don't ask. Decide.

I think users will think, "Oh, hey, my subscription starts/ends on April 17..."

So if I just use Dates - per #1 - then while 2019-04-17 to 2020-04-17 would technically be one year and one day, I think most people would see it as one year.

And excluding nit-picking, you could argue that implicitly going from April-7 to April-17 handles the Time too.

But would it break anything in my database if I have records like...

+----+-----------+---------+------------+------------+
| id | member_id | plan_id | start_date | end_date   |
+----+-----------+---------+------------+------------+
| 1  | 25        | 3       | 2019-04-17 | 2020-04-17 |
| 2  | 25        | 3       | 2020-04-17 | 2021-04-17 |
| 3  | 25        | 3       | 2021-04-17 | 2022-04-17 |
+----+-----------+---------+------------+------------+

 

 

 

11 minutes ago, requinix said:

Here's a question: does it actually matter? Think about it.

Well, like I said, if a user subscribed in the future relative to my server (e.g. 2020-04-17 4:00am UTC) and the record was stored in my database showing my time (e.g. 2020-04-16 11:00pm EST), then my PHP code wouldn't let them log in and get "premium access" until it was April 17 n my server, thus forcing the customer to wait 5 hours for the U.S. time to catch up with Europe?!

Right?

 

[kicken]

27 minutes ago, SaranacLake said:

Well, like I said, if a user subscribed in the future relative to my server (e.g. 2020-04-17 4:00am UTC) and the record was stored in my database showing my time (e.g. 2020-04-16 11:00pm EST), then my PHP code wouldn't let them log in and get "premium access" until it was April 17 n my server, thus forcing the customer to wait 5 hours for the U.S. time to catch up with Europe?!

You should only be using one timezone in all your code and storage.  UTC works well but pick whatever you want and use it. 

By using a single timezone and datetime types then most of this problem goes away.  Regardless of what the users local timezone is, you store the date and time in your chosen timezone.  Then whenever you want to compare to it you also compare the current time in your chosen timezone to that stored date and time.  If you want users to view dates and times in their timezone then convert them from your chosen timezone to their timezone just prior to display.

If your using generated times then things are pretty easy in general.  If your taking user-submitted dates and times, then things can potentially get a little more complex.  If you want users to deal with things according to their local time you'll need to convert their input from their timezone to yours before doing and processing.

 

2 hours ago, SaranacLake said:

If so, does a Subscription have a start_date of "2020-04-17" and an end_date of "2020-04-16" or an end_date of "2020-04-17"?

This is just something you need to decide as one of your business rules.  You also need to decide if whatever date you pick is inclusive or exclusive.  If I were to subscribe on 2020-04-17 @ 5:20, for one year then does my subscription run 2020-04-17 @ 5:20 -> 2021-04-17 @ 5:20?  2020-04-17 @ 0:00 -> 2021-04-17 0:00?  2020-04-17 @ 0:00 -> 2021-04-17 @ 23:59:59?  Up to you to decide.

If it were me, I'd probably go for for the last option as I think it's the most intuitive and what people would expect.   People are not going to remember the exact minute they signed up, and on the scale of a year I wouldn't nit-pick a few hours.

 

[SaranacLake]

3 minutes ago, kicken said:

You should only be using one timezone in all your code and storage.  UTC works well but pick whatever you want and use it. 

By using a single timezone and datetime types then most of this problem goes away.  Regardless of what the users local timezone is, you store the date and time in your chosen timezone.  Then whenever you want to compare to it you also compare the current time in your chosen timezone to that stored date and time.  If you want users to view dates and times in their timezone then convert them from your chosen timezone to their timezone just prior to display.

If your using generated times then things are pretty easy in general.  If your taking user-submitted dates and times, then things can potentially get a little more complex.  If you want users to deal with things according to their local time you'll need to convert their input from their timezone to yours before doing and processing.

Forgot my brain tonight! 😀

Since this is a WEBSiTE, I guess everything the user does is ultimately on my server and thus using my local time.

(I guess if I had something like a receipt or show the details in the user's profile I could "localize" the time, but that probably isn't such a big deal as long as I display the time zone (e.g. EST).

So I guess this would be a non-issue.

 

 

3 minutes ago, kicken said:

This is just something you need to decide as one of your business rules.  You also need to decide if whatever date you pick is inclusive or exclusive.  If I were to subscribe on 2020-04-17 @ 5:20, for one year then does my subscription run 2020-04-17 @ 5:20 -> 2021-04-17 @ 5:20?  2020-04-17 @ 0:00 -> 2021-04-17 0:00?  2020-04-17 @ 0:00 -> 2021-04-17 @ 23:59:59?  Up to you to decide.

If it were me, I'd probably go for for the last option as I think it's the most intuitive and what people would expect.   People are not going to remember the exact minute they signed up, and on the scale of a year I wouldn't nit-pick a few hours.

I'm not looking to split hairs, so I would round up until right before Midnight (i.e. 23:59:59).

But what do you think about using just DATES versus DATE&TIME?

Is there any downside to using TIME also?

I guess I was just afraid that it would complicate things.  For example, to use your example, if you subscribed on 2020-04-17 @ 5:20pm, I guess I would insert thate xact time as the "start_date" but then how would I easily insert an "end_date" of 2020-04-17 @ 23:59:59?

Maybe it is as easy as grabbing the DATE from the TIMESTAMP and then using my PHP code to append on a "23:59:59" to the DATE?

Or is there a PHP or MySQL function that would do this for me?

 

Any other thoughts on how to best handle all of this?

 

[SaranacLake]

@kicken,

 

Hello.  Could you please respond to my comments and questions in the post above? 

https://forums.phpfreaks.com/topic/310266-handling-subscription-dates/?do=findComment&comment=1575362

[requinix]

On 3/10/2020 at 10:38 PM, SaranacLake said:

Since this is a WEBSiTE, I guess everything the user does is ultimately on my server and thus using my local time.

I think you missed the point. It doesn't matter what timezone the user is in because you're recording and comparing dates on the server. A duration of one year is a duration of one year for everyone.

On 3/10/2020 at 10:38 PM, SaranacLake said:

(I guess if I had something like a receipt or show the details in the user's profile I could "localize" the time, but that probably isn't such a big deal as long as I display the time zone (e.g. EST).

Please localize it.

On 3/10/2020 at 10:38 PM, SaranacLake said:

I guess I was just afraid that it would complicate things.  For example, to use your example, if you subscribed on 2020-04-17 @ 5:20pm, I guess I would insert thate xact time as the "start_date" but then how would I easily insert an "end_date" of 2020-04-17 @ 23:59:59?

Don't record the end time at all. It starts at a particular moment, it goes up until the date that's one year later, and you check for validity using a <= so that the end date is also considered valid. It will give the user slightly more than one year, and depending on timezones that extra may or may not be noticeable to the user, but who cares.

[SaranacLake]

11 hours ago, requinix said:

I think you missed the point. It doesn't matter what timezone the user is in because you're recording and comparing dates on the server. A duration of one year is a duration of one year for everyone.

Right, that is the same conclusion that I came to!  😉

 

 

11 hours ago, requinix said:

Please localize it.

So I would store everything in my database as my local time of EST, and then use PHP to localize the dates in the user's control panel?

 

 

11 hours ago, requinix said:

Don't record the end time at all. It starts at a particular moment, it goes up until the date that's one year later, and you check for validity using a <= so that the end date is also considered valid. It will give the user slightly more than one year, and depending on timezones that extra may or may not be noticeable to the user, but who cares.

A few things here...

1.) So it sounds like you are saying that I should use a DATE and TIME and record the moment they make a purchase as the 'start date", right?  (I guess I could use a MySQl "TIMESTAMP" datatype for that?)

 

2.) My database design has a "start_date' and an 'end_date" for a subscription - otherwise how would I know when a user's subscription had ended?

So I think what you are saying is on registration/renewal to grab the TIMESTAMP and stick that in the "start_date" and then maybe use a date function in PHP or MySQL to add 1 year onto that 'start_date" to calculate my 'end_date" - which would need to be written into my "Member_Subscription_History" table.  

Is that what you were suggesting?