Going CRAZY over TimeZone issue

How To
1

I am working on a program to calculate time for employees who (basically) get paid by the job. I have tables in MySQL for their bookings (Table 1) and their actions (Table 2). I intend to use Make to pull in the data and calculate the correct hours.

In MySQL, the data shows the hours in local time, but when I use any of the MySQL modules in Make… it subtracts 5 hours from the actual dates (which show up correctly in MySQL).

I’m using MySQL Execute a Query with the following text in the Query field:
SELECT bookingID, Min(pickupAt), pickupLAT, pickupLNG, destLAT, destLNG, driverRef,acctRef FROM bookings
GROUP BY driverRef
ORDER BY Min(``pickupAt``) ASC

It is correctly giving me the earliest booking record for each driver. However, where the source data shows this:
|Booking ID | Pick Up Date/Time
|40272423A | 2024-04-14 00:34:00 |
|40272936A | 2024-04-14 02:38:00 |

It shows up in the Bundle (collections) like this:

  • bookingID: 40272423A

  • Min(pickupAt): April 13, 2024 7:34 PM

  • bookingID: 40272936A

  • Min(pickupAt): April 13, 2024 9:38 PM

I’ve tried everything I can think of (mostly various ways to ensure that MySQL knows what timezone to use - and including trying to ignore the problem. But, as I work through the programming and troubleshooting to ensure I’m isolating the correct records to make the calculations I need… it’s driving me crazy that I can’t just compare what’s in Make to what’s in the source documentation and/or my MySQL tables.

My Organization and Scenario timezone is set to America/Phoenix and my local timezone is set to America/Chicago (when all three are set to Phoenix… the datetime(s) shown are off by 7 hours instead of 5).

I can tell that there are some really smart people out there answering questions. Any thoughts on my problem here? I’d be glad to provide screen prints if that would be helpful… but I don’t see how and copy/paste didn’t work for me…

Since moving my local time from Phoenix to Chicago changed what I was getting by two hours… it seems like maybe I can set my local time to whatever would be five hours later than Chicago time… but that seems like a poor solution that probably comes with side effects??

2

I think the issue is that

  • the timezone in SQL is different to your local timezone

  • the date time shouldn’t be stored as a string in your database. You should be storing it as a timestamp like 1714956364, which makes it timezone-agnostic (timezone not needed).

Since it’s stored as a string (NOT recommended), you’ll need to use the built-in function parseDate with the optional third parameter for timezone, where you will insert the timezone of the SQL server.

For more information, see https://www.make.com/en/help/functions/date---time-functions#parsedate--text--format---timezone--

samliew – request private consultation

Join the Make unofficial Discord server!

3 Likes
3

Hi samiliew! Thank you for taking notice of my question!

I have a few additional questions based on your answer…

The dates are stored in MySQL as DateTime (they may have looked like text because I pasted values into the question to illustrate). Are you suggesting I should change the type on that field to Timestamp? It is one of the options I can pick, but I don’t understand how that would make it show up as a recognizable date in the MySQL Query results in Make… But, of course, I’m glad to try it if you’re suggesting I do that!

But, regardless of whether it’s text or a date, is there somewhere in the API call I can insert the function that would have an impact on how the results (Bundles/Collections output) of the MySQL “Execute a Query” API show up?

Just because I was curious… I decided to try setting the Web timezone on my Profile to a timezone that shows up as GMT+00:00 to see what would happen (Organization and Scenario timezones are still set to America/Phoenix). That actually caused the dates to show up in the Bundle/Collections output correctly. Any idea why?

4

Timestamps are always recommended because you won’t have to deal with timezones at all between systems.

You can also do calculations in math, since it’s stored in seconds.

One day: 24 * 60 * 60

Is later than one day ago: timestamp > now - 24 * 60 * 60

1 Like
5

Thank you for taking time with my issue and sharing your knowledge to help me. I changed all the data fields to timestamps but that didn’t change how the data is showing up. I think it’s time to move on. I’ll set my Web timezone to GMT 00:00 while I get the logic worked out and I’ll throw in formatDate functions at my first opportunity. Thank you, sincerely, for your help!

1 Like
6

If you need further assistance, screenshots would help us see what you’re looking at.

1 Like
7

OK - well I’m sure this is a stupid question… but how do I add screen prints? I seem to have only very basic tools within this conversation. There’s an Upload button, but attempting to upload a screenprint seems to insert a command? rather than an actual screenshot? Perhaps it turns to a picture after I click Reply? I’m always game to try :slight_smile: :grinning: Have a wonderful day!

Screenshot 2024-05-05 at 10.38.59 PM
Screenshot 2024-05-05 at 10.38.59 PM1920×1200 260 KB

8

And look at that… it appears when you send it…

Thank you again!

9

I wonder if the SQL date time field is storing data with GMT +00:00? If it is showing the “right” time to you, it may still be the fact that the local and the storage timezone is GMT+00:00 on your SQL server.

When it comes into Make if that’s the case, the user (aka Web) timezone will convert that. If you look at your output bundle in Make for the SQL module you should see if it is +00:00 or Z at the end. If there’s no timezone, Make may be defaulting to GMT +00:00.

That’s why setting your user (Web) time zone shows you the “right” time because Make is not adjusting the display of dates at all, effectively aligning your user timezone with the timezone embedded into the underlying data in SQL.

There’s also one more issue to consider: your data from SQL doesn’t indicate whether the data is using a 24 hour clock. I think it does because there is no AM/PM component. Make is assuming all your data is PM though, but it appears from the output out of SQL the data is AM in a 24 hour clock.

All the more reason that you most likely are passing a text value out of SQL into Make and you’ll need to parseDate() on these values to correctly put them into the right time zone (probably GMT +00:00) and into a 24 hour clock.

Have a look at the input variables of this simple scenario that uses scenario inputs. If you don’t have these you can just replace the {{now}} variable and use 2024-04-14 00:34:00 for var3.

You can see that Make behind the scenes converts the data from the local time into Zulu/GMT time. That’s why there is a Z at the end of each variable in the input and output bundles.

Click to view and copy the blueprint into your scenario 
{
    "subflows": [
        {
            "flow": [
                {
                    "id": 1,
                    "module": "util:SetVariables",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "variables": [
                            {
                                "name": "var1",
                                "value": "{{var.input.var1}}"
                            },
                            {
                                "name": "var2",
                                "value": "{{var.input.var2}}"
                            },
                            {
                                "name": "var3",
                                "value": "{{parseDate(var.input.var3; \"YYYY-MM-DD HH:mm:ss\")}}"
                            }
                        ],
                        "scope": "roundtrip"
                    },
                    "metadata": {
                        "designer": {
                            "x": 0,
                            "y": 0
                        },
                        "restore": {
                            "expect": {
                                "variables": {
                                    "items": [
                                        null,
                                        null,
                                        null
                                    ]
                                },
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "variables",
                                "type": "array",
                                "label": "Variables",
                                "spec": [
                                    {
                                        "name": "name",
                                        "label": "Variable name",
                                        "type": "text",
                                        "required": true
                                    },
                                    {
                                        "name": "value",
                                        "label": "Variable value",
                                        "type": "any"
                                    }
                                ]
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            }
                        ],
                        "interface": [
                            {
                                "name": "var1",
                                "label": "var1",
                                "type": "any"
                            },
                            {
                                "name": "var2",
                                "label": "var2",
                                "type": "any"
                            },
                            {
                                "name": "var3",
                                "label": "var3",
                                "type": "any"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}```
2 Likes
10

Looking at the pickupAt column it is impossible to tell what the timezone is of this data, as it is being formatted out in the display. But it’s a lot more important to know what is going into Make and if there is no timezone information in the date value, then the default may be set to GMT+00:00

So if you KNOW your data is in GMT-0500 you can use this parseDate function to cast the string value coming into Make into the right time zone

{{parseDate(var.input.var3; "YYYY-MM-DD HH:mm:ss"; "America/Toronto")}}

The parseDate docs have all the timezone strings in the TZ Database Name column

If you omit the TimeZone token in the parseDate() function it defaults to the Organization’s timezone, which is probably what you want assuming the data in SQL server was collected in the same timezone as the Org’s timezone.

1 Like
11

What’s a Scenario time zone? I thought Make only has an Organization time zone and a Web (aka user, aka local) timezone.

1 Like
12

Thank yo for everything you’ve sent. It’s going to take me a little while to go through it… but let me answer two things you ask/talk about in your responses. When this data comes into Make in bundles (using the MySQL API), shows it as a long date w/ time formatted for AM/PM - even though, yes, I am working with a 24 hour clock.

Screenshot 2024-05-01 at 11.11.48 PM
Screenshot 2024-05-01 at 11.11.48 PM907×1009 77 KB

Also, I don’t know what a Scenario data/time is… but I have one on my profile, as you can see below.

Screenshot 2024-05-09 at 5.15.21 PM
Screenshot 2024-05-09 at 5.15.21 PM2288×1068 152 KB

Thank you again for your willingness to engage with my issue! I’ll look at what you sent as soon as I can, and I have a feeling that even though my workaround with setting the Web Timezone to GTM 00:00 has solved my problem for now… I have the feeling my challenges are not over!!!

13

Just was watching your video - “Make - Experimenting with Variables, Arrays, Iterators and Aggregators”. SO HELPFUL! And, I can’t wait to take out the Array aggregators and simplify my scenario by using the initial collections with an iterator :slight_smile: .

In addition to learning just what I needed on my latest challenges… I saw how I can see how Make is seeing my dates. Here is an example: “DriverActAt”: “2024-05-06T10:58:22.000Z”

Thoughts?

14

Yes that’s Zulu time or gmt +00:00. That’s why when you put your system into gmt time you see the right time. If you put your local web time zone into something different then you’ll see the time in your local time zone. Your database is storing the data in gmt time.

15

Good I’m glad someone found them useful. I make them from time to time to explain things that just can’t be explained in words so easily. Make is a very visual system.

–
Alex Sirota
Director of NewPath Consulting - we are :superhero: Make Heroes! :woman_superhero:t4:

:heart_on_fire: Check out my series of videos and scenario walkthroughs for Make Newbies :heart_on_fire:

My Solutions on Make Community