query_timezone.py

# -*- coding: utf-8 -*-

import arrow
import MySQLdb

db = MySQLdb.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='root',
    db='test'
)

cursor = db.cursor()

def insert_data():
    # 记录的是 时间戳，时间戳和时区没有关系
    data = [
        (1, arrow.Arrow(2015, 9, 22, 22, 0, 0, tzinfo='Asia/Shanghai').timestamp),
        (2, arrow.Arrow(2015, 9, 22, 23, 0, 0, tzinfo='Asia/Shanghai').timestamp),
        (3, arrow.Arrow(2015, 9, 23, 0, 0, 0, tzinfo='Asia/Shanghai').timestamp),
        (4, arrow.Arrow(2015, 9, 23, 1, 0, 0, tzinfo='Asia/Shanghai').timestamp),
    ]

    cursor.executemany(
        "INSERT INTO test (id, timestamp) VALUES (%s, %s)",
        data
    )

    db.commit()

def query(tz='UTC'):
    # 查找当天的记录
    now = arrow.utcnow().to(tz)
    now_date = arrow.Arrow(now.year, now.month, now.day, tzinfo=now.tzinfo)

    cursor.execute(
        "SELECT * FROM test where timestamp >= %s AND timestamp <= %s",
        (now_date.timestamp, now.timestamp)
    )

    result = cursor.fetchall()
    print "TIMEZONE =", tz
    print result


def main():
    cursor.execute("SELECT COUNT(*) FROM test")
    result = cursor.fetchone()[0]
    if result == 0:
        insert_data()

    query('UTC')
    query('Asia/Shanghai')


if __name__ == '__main__':
    main()


# OUTPUT
#
# TIMEZONE = UTC
# ()
# TIMEZONE = Asia/Shanghai
# ((3L, 1442937600L), (4L, 1442941200L))