在mysql查询中使用IST时间 [英] Using IST time in mysql query
问题描述
示例
select * from mytable where dt> current_ist_datetime
current_ist_datetime
是我不知道的如何使用。 CONVERT_TZ() NOW()可以给它但我不知道什么时区mysql使用。也就是说,我不知道第二个参数我应该传递给 CONVERT_TZ()
MySQL以两种格式使用时区:即可以使用亚洲/加尔各答或+05:30(IST)。但是我建议你使用后者。
UTC的时区为'+00:00'。因此,您可以使用以下查询,只要 dt
在 UTC
& current_ist_datetime
在 IST
select * from mytable where dt> CONVERT_TZ(current_ist_datetime,'+ 05:30','+ 00:00')
我正在使用这个查询在我的项目中我paas current_ist_datetime
&从客户端(IST)到我的服务器(以UTC为单位)的 +05:30
它的作用就像一个魅力。
如果您使用 NOW()
mysql将选择系统时区的当前日期时间
select * from mytable where dt> CONVERT_TZ(NOW(),'+ 05:30','+ 00:00')
你可以根据需要修改这些查询。
I have a table containing datetime field. I want to select records with a condition that only those records that has datetime value above current IST time.
Example
select * from mytable where dt > current_ist_datetime
current_ist_datetime
is what i do not know how to use. CONVERT_TZ() with NOW() can give it. But i do not know what timezone mysql uses. that is, i do not know second parameter that i should pass to CONVERT_TZ()
MySQL uses timezone in two formats; i.e. you can either use 'Asia/Calcutta' or '+05:30' (for IST). But I would suggest you use the latter one.
Timezone for UTC is '+00:00'. So you can use following query provided that dt
is in UTC
& current_ist_datetime
is in IST
select * from mytable where dt > CONVERT_TZ(current_ist_datetime,'+05:30','+00:00')
I am using this query in my project where I paas current_ist_datetime
& +05:30
from client(in IST) to my server(in UTC) & it works like a charm.
If you use NOW()
mysql will select current date time of your system's timezone
select * from mytable where dt > CONVERT_TZ(NOW(),'+05:30','+00:00')
You can modify these queries according to your need.
这篇关于在mysql查询中使用IST时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!