Re: [Dev] Storing TimeZone neutral times with Mysql

[Hasitha Aravinda]

Hi Vinod,

I checked MSSQL [1], Postgres [2], DB2 [3], and Oracle [4] documentations
and their max supported time-stamp is . It seems like only MySQL has
this problem.

IMO, we should support time-stamp values beyond 2038, if database supports
it. So shall we add a logic to determine DB type and if DB is MySQL, then
support up to its max time-stamp, if DB is another type, then simple allow
 as Max time-stamp.

Issue in datetime format is, it doesn't support timezone values (Expect for
MSSQL). Since we are considering use-case in different time zones, I think
we have to go with time-stamp.

[1] - https://msdn.microsoft.com/en-us/library/ms187819.aspx
[2] - https://www.postgresql.org/docs/9.2/static/datatype-datetime.html
[3] -
https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_limits.html
[4] - http://www.techonthenet.com/oracle/datatypes.php

Thanks,
Hasitha.

On Wed, Jul 20, 2016 at 11:41 AM, Vinod Kavinda  wrote:

> Hi all,
> I have a requirement that I need to store timestamp values where it will
> be required to retrieve it in different time zones. So in MySql, I used
> timestamp data type where MySQL store  the time in UTC and return in local
> timezone.
>
> The problem is when the time is null, I need to represent 'forever'.
> Thought of setting the max date possible in epoch time (Long.Max) for this.
> But the max possible timestamp value in MySql is year 2038/1/19... [1].
>
> So, is this 2038 is fair enough as the max date? (This will be the max
> date for other DBMS also.)
>
> Should I use DateTime datatype instead and handle the timezone stuff in
> application level?
>
> What is the best practice used here in Wso2?
>
>
> [1] - https://dev.mysql.com/doc/refman/5.5/en/datetime.html
>
> Regards,
> Vinod
> --
> Vinod Kavinda
> Software Engineer
> *WSO2 Inc. - lean . enterprise . middleware .*
> Mobile : +94 (0) 712 415544
> Blog : http://soatechflicks.blogspot.com/
> [image: http://wso2.com/signature]
> 
>
>


-- 
--
Hasitha Aravinda,
Associate Technical Lead,
WSO2 Inc.
Email: hasi...@wso2.com
Mobile : +94 718 210 200
___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev

[Dev] Storing TimeZone neutral times with Mysql

[Vinod Kavinda]

Hi all,
I have a requirement that I need to store timestamp values where it will be
required to retrieve it in different time zones. So in MySql, I used
timestamp data type where MySQL store  the time in UTC and return in local
timezone.

The problem is when the time is null, I need to represent 'forever'.
Thought of setting the max date possible in epoch time (Long.Max) for this.
But the max possible timestamp value in MySql is year 2038/1/19... [1].

So, is this 2038 is fair enough as the max date? (This will be the max date
for other DBMS also.)

Should I use DateTime datatype instead and handle the timezone stuff in
application level?

What is the best practice used here in Wso2?


[1] - https://dev.mysql.com/doc/refman/5.5/en/datetime.html

Regards,
Vinod
-- 
Vinod Kavinda
Software Engineer
*WSO2 Inc. - lean . enterprise . middleware .*
Mobile : +94 (0) 712 415544
Blog : http://soatechflicks.blogspot.com/
[image: http://wso2.com/signature]

___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev