1

I am developing an application that needs to record the timestamp in an Sql column(default set to CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) whenever any database operation takes place.

After deploying the application on App Engine, I do get timestamp in the Sql column but I have no idea where it's picking the timestamp from(I guess it's the timestamp from the server the app is running on or Google Cloud SQl is running on).

If I need timezone information on the same, how can I get it ? Or is there a better way to record such information like generating the timestamp using java code java.util.Date date= new java.util.Date(); and storing the same in sql column and which timezone/time it will pick then ?

Raghvendra Kumar
  • 1,328
  • 1
  • 10
  • 26

3 Answers3

0

By default, time is usually stored in GMT. When you retrieve it you can use the following:
select datetime(current_timestamp, 'localtime'); and it should you the time in your timezone. That was just an example, there's obviously variations to that. There's many ways to go about this actually, you could use a Java time stamp set to a specific time zone and store that instead.

Gwell
  • 271
  • 1
  • 5
  • You mean to say that the App Engine(Google Server) on which my app is deployed uses GMT as time zone ? Are you sure of that ? Well I will try the select query that you have provided and see if that helps. Thanks – Raghvendra Kumar Aug 04 '14 at 16:52
  • To be more precise it's UTC, not GMT, but they usually are interchangeable, though UTC is what is used. – Gwell Aug 04 '14 at 16:59
  • Okay. I checked the log from app Engine console and I see that it's UTC indeed, but do you know of a way to get the time and date or time zone info of the user who is accessing the application recorded in the database because using java `java.util.Date().getTime();` would give me the info of the server or generating this even in the database by default would even generate as per the server's time. – Raghvendra Kumar Aug 05 '14 at 10:22
  • Exactly, the java code would be server side and give you the server's time, so what you could do is get it from javascript which runs client side. Take a look at this: http://stackoverflow.com/questions/1091372/getting-the-clients-timezone-in-javascript – Gwell Aug 05 '14 at 12:50
  • Thanks for the link, Gwell. – Raghvendra Kumar Aug 05 '14 at 16:02
0

I don't know how you use this data, but the most efficient approach is to store java.util.Date().getTime(); Long values in the database. It's enough for most use cases, like comparing data entities by their creation time, logs, etc.

Andrei Volgin
  • 40,755
  • 6
  • 49
  • 58
  • I understand that using `java.util.Date().getTime(); ` is useful but the concern here is that when the user access the deployed app on app engine, the calculations will be based on server's cpu for date and time and my interest here is to get the time and date of the user or even the the timezone zone info of the user who is accessing the app and through the app performing database operations. – Raghvendra Kumar Aug 05 '14 at 10:18
  • Date.getTime() is a Long. There is no time zone involved in any calculations, CPU or otherwise. There is also no time zone in a Java Date object. The only time a time zone is applied is when you print a Date on a server or display it on your client. – Andrei Volgin Aug 05 '14 at 16:20
0

Since the application runs on GAE after deploying and the GAE server is set to GMT, using java to generate the timestamp or even setting the table on the cloud sql to generate timestamp automatically, will be as per the server's time and therefore is not useful for my application.

So, as a work around, in order to record the timestamp of a user accessing the application, I am generating timestamp through javascript using new Date(); and storing the same as a string in the sql table on the cloud.

Well, this is half of my requirement as I currently have no way to display this stored timestamp to a user according to his time zone, which could be different from what's stored in the table. Thanks

Raghvendra Kumar
  • 1,328
  • 1
  • 10
  • 26