Re: Shifting dates
From: Keith Ivey Jigal van Hemert wrote: Maybe because dates before Jan 1, 1970 have an undefined timestamp and dates beyond 2038 cannot be used with 32-bit integers? Quite a few people were born before 1970 and sometimes one needs to store their date of birth too? Yes, but birthdates are generally DATE, not DATETIME, unless you're doing astrology. Sebastian was talking about DATETIME versus Unix timestamp INT. You can find imperfections in all examples. Fact remains that the range of a Unix timestamp is way too limited for many purposes. OTOH Unix timestamps were designed for timestamps related to files and in that the case the range is not much of a limit. I find that for certain applications that DATE and DATETIME both lack a large enough range and sufficient resolution. Historical dates BC cannot be stored, nor can you use a resolution of less than a second. Also the problems with DATE(TIME) and daylight saving are not very amusing. Still the larger range and all the functions available for handling DATE(TIME) values make me use them more often than unix timestamps. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
On 6/21/05, Sebastian wrote: i never understand why people use datetime anyway.. unix timestamp is so much easier to work with. Unix epoch is by definition UTC. Sometimes I want to work with dates in some local timezone. In other databases that have a more complete implementation of the SQL standard you can do really neat tricks with that. Just look at the following examples from PostgreSQL: jochemd= select '2005-06-15 00:00:00'; ?column? 2005-06-15 00:00:00 jochemd= select '2005-06-15 00:00:00' AT TIME ZONE 'PDT'; timezone - 2005-06-14 17:00:00 jochemd= set TimeZone = 'EST'; jochemd= select '2005-06-15 00:00:00'; ?column? - 2005-06-15 00:00:00 jochemd= select '2005-06-15 00:00:00' AT TIME ZONE 'PDT'; timezone - 2005-06-14 22:00:00 While I can't use this functionality in MySQL (yet?), I use it enough in other databases to always use a timestamp datatype instead of a epoch to keep code as uniform as possible. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
Hi,
just see the client connection timezone here :
http://dev.mysql.com/tech-resources/articles/4.1/time.html
you should certainly use --default-time-zone='-3:00'
Mathias
Selon Simon Garner [EMAIL PROTECTED]:
On 21/06/2005 2:45 p.m., Scott Haneda wrote:
I need to run a BETWEEN select where I put in a date rate, the time was at
one point irrelevant, but now the client is in a new time zone +3 hours
ahead, so
BETWEEN 2005010100 AND 20051201235959 is what I pass in now, which is
wrong, how can I add three hours to it and get the days and months to wrap
as needed.
I would love to do this in SQL, not in application.
Try something like:
SELECT
*
FROM
table
WHERE
datefield '2005-01-01 00:00:00'
AND datefield DATE_ADD('2005-01-01 00:00:00', INTERVAL 3 HOUR)
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
-Simon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
i never understand why people use datetime anyway.. unix timestamp is so
much easier to work with.
[EMAIL PROTECTED] wrote:
Hi,
just see the client connection timezone here :
http://dev.mysql.com/tech-resources/articles/4.1/time.html
you should certainly use --default-time-zone='-3:00'
Mathias
Selon Simon Garner [EMAIL PROTECTED]:
On 21/06/2005 2:45 p.m., Scott Haneda wrote:
I need to run a BETWEEN select where I put in a date rate, the time was at
one point irrelevant, but now the client is in a new time zone +3 hours
ahead, so
BETWEEN 2005010100 AND 20051201235959 is what I pass in now, which is
wrong, how can I add three hours to it and get the days and months to wrap
as needed.
I would love to do this in SQL, not in application.
Try something like:
SELECT
*
FROM
table
WHERE
datefield '2005-01-01 00:00:00'
AND datefield DATE_ADD('2005-01-01 00:00:00', INTERVAL 3 HOUR)
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
-Simon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
On 21/06/2005 6:55 p.m., Sebastian wrote: i never understand why people use datetime anyway.. unix timestamp is so much easier to work with. Because DATETIME is stored natively as a date and time, which means you can then use the date and time SQL functions with them (such as DATE_ADD, DATE_SUB, DATE_FORMAT etc). If you just store your date as a timestamp in an INT then you first have to convert it into a DATETIME using FROM_UNIXTIME. Admittedly not difficult, but an additional and unnecessary conversion. For me it's really just a matter of rightness - why store a date as an int when you can store it as a date? :) Also a question of whether you prefer to do date manipulations in your application or at the SQL level. But both ways work. -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
From: Sebastian i never understand why people use datetime anyway.. unix timestamp is so much easier to work with. Maybe because dates before Jan 1, 1970 have an undefined timestamp and dates beyond 2038 cannot be used with 32-bit integers? Quite a few people were born before 1970 and sometimes one needs to store their date of birth too? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
Jigal van Hemert wrote: Maybe because dates before Jan 1, 1970 have an undefined timestamp and dates beyond 2038 cannot be used with 32-bit integers? Quite a few people were born before 1970 and sometimes one needs to store their date of birth too? Yes, but birthdates are generally DATE, not DATETIME, unless you're doing astrology. Sebastian was talking about DATETIME versus Unix timestamp INT. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Shifting dates
I need to run a BETWEEN select where I put in a date rate, the time was at one point irrelevant, but now the client is in a new time zone +3 hours ahead, so BETWEEN 2005010100 AND 20051201235959 is what I pass in now, which is wrong, how can I add three hours to it and get the days and months to wrap as needed. I would love to do this in SQL, not in application. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
On 21/06/2005 2:45 p.m., Scott Haneda wrote:
I need to run a BETWEEN select where I put in a date rate, the time was at
one point irrelevant, but now the client is in a new time zone +3 hours
ahead, so
BETWEEN 2005010100 AND 20051201235959 is what I pass in now, which is
wrong, how can I add three hours to it and get the days and months to wrap
as needed.
I would love to do this in SQL, not in application.
Try something like:
SELECT
*
FROM
table
WHERE
datefield '2005-01-01 00:00:00'
AND datefield DATE_ADD('2005-01-01 00:00:00', INTERVAL 3 HOUR)
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
-Simon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
