Handling User Local Time in AWS Lambda for Streak Feature

Storing all timestamps in UTC in your database is indeed a good practice for consistency. When handling user local time in AWS Lambda for features like streaks, here are some approaches to consider:

Storing User Timezone Information: Yes, storing the user's timezone in your database is a recommended approach. This allows you to perform accurate time conversions in your Lambda function. You could store this as a timezone identifier (e.g., "America/New_York") rather than just an offset, as this handles daylight saving time transitions correctly.

Updating Timezone Information: It's a good idea to check and potentially update the user's timezone when they open the app, especially if they might travel between time zones. You could implement this as part of your app's startup flow, comparing the current device timezone with the stored value and updating if necessary.

Handling Time Conversions in Lambda: For your Lambda function, you have a few options:

1. Use the stored timezone identifier to convert UTC timestamps to the user's local time. Most programming languages have libraries to handle this conversion.

2. For MySQL specifically, you could set the time_zone parameter in your RDS instance's parameter group. However, this would apply globally to the database, which might not be what you want if you're serving users in multiple time zones.

3. If you're using Amazon Location Service, you could use the Geocode or Reverse Geocode APIs with the TimeZone additional feature to get timezone information based on a user's address or coordinates.

Implementation Example: In your Lambda function, you might do something like:

// Retrieve user's timezone from your database (e.g., "America/New_York")
const userTimezone = getUserTimezoneFromDatabase(userId);

// Convert UTC timestamp to user's local time
const utcTimestamp = getTimestampFromDatabase();
const userLocalTime = convertToTimezone(utcTimestamp, userTimezone);

// Compare with current time in user's timezone
const now = getCurrentTimeInTimezone(userTimezone);
const isStreak = compareForStreak(userLocalTime, now);

This approach keeps your database in UTC while allowing accurate local time comparisons for features like streaks.
Sources
TimeZone identifier | AWS re:Post
Local time zone for MySQL DB instances - Amazon Relational Database Service