After some testing, here is our findings:
TIMESTAMP in DB Insert inserts 0s (whether field type be DATE or DATETIME)
NOW and NOW_UTC insert the server’s time, not the user’s UTC.
Function toLocalTime doesn’t change the date/time.
Function toUTCTime gives error “Class ‘lib\core\DateTimeZone’ not found”
DB: MySQL
PHP version: 7.3.6
Anyone can advise how to make sure the user’s UTC is inserted in the DB? Thanks.
NOW_UTC should insert the current UTC time which is the same as GMT so should align to your time - 1hour
NOW will return the current time set on the server so it depends on where the server is located
If you want to set a local time and the server is in another time zone then best use NOW_UTC and add the time zone offset accordingly using Date Add
Thanks Brian but NOW_UTC does not record the user’s UTC.
Example below where I recorded data at 5:25pm FRA time under NOW_UTC but time was recorded per server time, 7h behind, located somewhere in the US.
Server actions run on the server. So all references to NOW or UTC are with respect to server date time settings.
Similarly, in DB, now() returns as per DB server’s date time settings.
To record information in user’s time zone, you will have to either pass the exact date time from client to server side. Or just pass the time zone to SA and calculate user’s time (this is a better option).
Or, you can just store everything in the DB in UTC, and based on user’s time zone, converts the date time on client side OR convert in SA (again by getting the time zone first… like on login) and return user-local date time value.
What do you mean users UTC? UTC is a constant
UTC is the new name for Greenwich meantime i.e. 0 timezone offset.
So it will always return what is effectively UK time so as we speak now 16:10 UK
It doesn’t matter where in the world the person is, NOW_UTC will always return that same time, the current time in the UK on the international date line at that instance.
It is designed to allow for a baseline/constant
If you want to capture the persons local time when making an entry you would have to pass the time from their browser via a $_POST or $_GET using the Date and Time component in App Connect
Yep, this is the method I use. Works wonderfully!
Can you try this simple test
Create a server API action as per this ensuring that Output is checked in each case
Then create a simple page like this.
Where the server connect calls the above server APi action
Set the paragraph content to this (just use the picker):
That will show the times returned from the server like this, in my case server time and UTC are the same as they are the same zone
Thanks!
What is the syntax for having the user’s present browser time.
I need the UTC offset of each user so need to calculate the difference.
Yes, either the offset for each user (not so easy as you ned to know where they are) or use the Date and Time component in App connect and send that via a GET/POST to the server action.
Easiest is add the time and date component to the page
The interval is just how often the component updates itself to the current time
It creates a variable, i renamed mine
So you end up with something like this
Then pass this to the appropriate server action
You can also do this from a form by POST by setting an hidden field to the value of the date time component than picking it up in the API action
All that is then needed is to insert the passed value into your database insert/update
Now i have both timings for UTC and local time and need to calculate the difference with the minus operator, so that FRA is recorded with a +01:00:00 time. What data conversion do I need in order to get my UTC offset calculated?
If you know the offset then use the “Date Add” function in server connect set in hours
If you use the browser method above the it will send their local time as set in the browser’s time zone settings i.e their lcoal time
Hi Wappler guys,
Thanks so much for your help on this, appreciated.
I tried the dateAdd function, but it add days and not hours. Is there a tutorial on how to use dateAdd properly?
For example here is the database value of the time, the timezone difference, and when I dateAdd(TZ) onto the database value. Instead of adding 4.5 hours, it adds many more days.
The binding of “Time in DB with addHours(TZ)” is dynamic and as follows. It adds the result of the number queryTZ to the result of the datetime query.
Is it because the datetime query is an (A) text to which I try to add a number? That datetime field is a datetime format in the database
PS you can see dateAdd works if it is a typed number and not a bound value. Though trying to add 4.5 hours only adds 4! Could you ament the function so it adds the decimal part into minutes?
Hi Wappler,
Coming back to this function, could you please let me know if I’m doing something wrong?
Thank you for your help as always 
Methods for adjusting to local time were also discussed here:
I understand and read that other thread already, very informative. That is the way I’m going, recording UTC time then adding the offset hours that I have recorded as a number (ex: 4 or 4.5)
Here I’m talking specifically about the addHours() which behaves erratically:
I appreciate that it looks like there’s something unusual going on with addHours but the method on the other thread works. It uses addSeconds rather than addHours so, if decimals are the issue, would solve that. In your case you could simply multiply TZ by 3600 to convert to seconds. I just thought it might be a solution to what you need, even if it doesn’t fix addHours.
Best work in minutes if you need half hour offsets
Can you show is exactly how you are trying to do that? I suspect the issue will we user error rather than wappler
Hey back. I’m 100% it’s not Wappler too but the way we are attempting this isn’t working yet.
Below is the “created_on” datetime field straight from the database, and then the same value with a addHours(1) where 1 is typed static number. Works (even with - negative numbers)
getConsThreadMsgs[0].created_on.addHours(1)
Now instead of a static value, I’m getting a dynamic offset based on the timezone the user is in. I use the free Bing Map API, and all timezone APIs return the offset in this format: X:XX
You pass the query:timezone (ex: America/New_York) and you get information back, including the utcOffetWithDst
For example, for America/New_York the offset comes back accurately to -4:00. Great.
Now when I use that value as dynamic in addHours() it doesn’t add well.
If I leave the value as is, it becomes NaN
If I transform the dynamic value to number, to date, to timestamp, there is no addition (still 9:31)
The quest remains, how to add a dynamic value that comes as “-4:00” (text) so that it can be added to a datetime value.
Maybe stick the value in a variable, and apply some function or straight php to that variable, to then use in the addHours function?
(PS it will need to also work for fractionals (ex: India 4:30) we’ll also need to convert that text to 4.5, before multiplying by 60 and using addMinutes() )
If you use replace() on the timezone (as it’s returned as a time not decimal) it might work:
pathto.utcOffsetWithDst.replace(':','.').toNumber()
For fractionals you might have to to some calculation with split()
(pathto.utcOffsetWithDst.split(':')[0]+'.'+(pathto.utcOffsetWithDst.split(':')[1] ? 60/pathto.utcOffsetWithDst.split(':')[1] : '0')).toNumber()
Yes, as @bph says, look like data type issue.
This is certainly not in numeric format, guessing it is typical time format i.e. 3:30. In that example it would need to be converted to a number…I recommend you use minutes so -3:30 would be -210 minutes. You can do this by using split() to split on the colon, say to an arrray time{} then time[0] will be the hours and time[1] will be the minutes. The calc is then simply time[0]* 60 + time[1] to get minutes as Ben says
