I’ve been asked to change some dates in a MYSQL database.
One date for example is 15 Feb 2009, and this appears in the database as 1234677600.
How does 15 Feb 2009 = 1234677600
Anyone been here before? :-/
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
Sometime around 21/3/08 (at 14:27 -0400) WebWorker said:
How does 15 Feb 2009 = 1234677600
Anyone been here before? :-/
Not yet - that’s still 11 months away! 
Try looking here for a bit of help:
k
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
Lets hope we all get there 
Thanks for that…
But does MYSQL automatically convert dates into this date stamp? Or do you have to have a PHP program converting a date into a date stamp before uploading to the database?
On 21 Mar. 2008, 6:35 pm, thatkeith wrote:
Sometime around 21/3/08 (at 14:27 -0400) WebWorker said:
How does 15 Feb 2009 = 1234677600
Anyone been here before? :-/
Not yet - that’s still 11 months away!
Try looking here for a bit of help:
http://www.4webhelp.net/us/timestamp.phpk
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
This looks like a Unix date, which is measured as the number of
seconds since January 1, 1970. PHP has the helpful tool strtotime(),
which will make a Unix date out of nearly any string. Yours becomes
1234674000.
Walter
On Mar 21, 2008, at 2:27 PM, WebWorker wrote:
I’ve been asked to change some dates in a MYSQL database.
One date for example is 15 Feb 2009, and this appears in the
database as 1234677600.How does 15 Feb 2009 = 1234677600
Anyone been here before? :-/
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
On the link Keith gave,
1234677600 = Sunday, February 15th 2009, 6:00:00 (GMT)
Whereas yours (on the same link) give:
1234674000 = Sunday, February 15th 2009, 5:00:00 (GMT)
One hour difference. Any reason why?
On 21 Mar. 2008, 6:48 pm, waltd wrote:
This looks like a Unix date, which is measured as the number of
seconds since January 1, 1970. PHP has the helpful tool strtotime(),
which will make a Unix date out of nearly any string. Yours becomes
1234674000.Walter
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
Not automatically, but there are query tools that you can use. If you
need to insert a timestamp from a query, you can use
INSERT INTO mytable (datefield) VALUES (UNIX_TIMESTAMP()) …
If you have stored the dates in this manner, and want them back out
as MySQL time, then you can use SELECT FROM_UNIXTIME(datefield) FROM
mytable … and you will get back a MySQL formatted date, like
2001-12-20 13:23:15.
There’s more here, including a way to format the result any way you
like using normal Date() formatting string magic, all within your SQL
call:
<http://dev.mysql.com/doc/refman/5.0/en/date-and-time-
functions.html#function_from-unixtime>
Walter
On Mar 21, 2008, at 2:44 PM, WebWorker wrote:
Lets hope we all get there
Thanks for that…
But does MYSQL automatically convert dates into this date stamp? Or
do you have to have a PHP program converting a date into a date
stamp before uploading to the database?On 21 Mar. 2008, 6:35 pm, thatkeith wrote:
Sometime around 21/3/08 (at 14:27 -0400) WebWorker said:
How does 15 Feb 2009 = 1234677600
Anyone been here before? :-/
Not yet - that’s still 11 months away!
Try looking here for a bit of help:
http://www.4webhelp.net/us/timestamp.phpk
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
Server was not adjusted for DST in one instance or the other.
Walter
On Mar 21, 2008, at 2:58 PM, WebWorker wrote:
On the link Keith gave,
1234677600 = Sunday, February 15th 2009, 6:00:00 (GMT)
Whereas yours (on the same link) give:
1234674000 = Sunday, February 15th 2009, 5:00:00 (GMT)
One hour difference. Any reason why?
On 21 Mar. 2008, 6:48 pm, waltd wrote:
This looks like a Unix date, which is measured as the number of
seconds since January 1, 1970. PHP has the helpful tool strtotime(),
which will make a Unix date out of nearly any string. Yours becomes
1234674000.Walter
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
Thanks - this gives me plenty to think about.
Hope I find some time
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
That is really fascinating. So that means that in addition to AD and
BC we could now think in terms of BU (before Unix) and say, AU (in
the year of Unix). So we are in the year 38AU. The computers will
probably think that anyway when they take over…
Pete
On 21 Mar 2008, at 18:48, Walter Lee Davis wrote:
This looks like a Unix date, which is measured as the number of
seconds since January 1, 1970. PHP has the helpful tool strtotime(),
which will make a Unix date out of nearly any string. Yours becomes
1234674000.Walter
On Mar 21, 2008, at 2:27 PM, WebWorker wrote:
I’ve been asked to change some dates in a MYSQL database.
One date for example is 15 Feb 2009, and this appears in the
database as 1234677600.How does 15 Feb 2009 = 1234677600
Anyone been here before? :-/
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
That moment is known as the Unix Epoch, and has no meaning other than
a simple point in the time-map to stick your pin. The gray-beards who
wrote Unix just picked it, and you can go forward and backward of the
epoch by using a suitably large positive or negative integer.
Walter
On Mar 21, 2008, at 3:37 PM, Pete MacKenzie wrote:
That is really fascinating. So that means that in addition to AD and
BC we could now think in terms of BU (before Unix) and say, AU (in
the year of Unix). So we are in the year 38AU. The computers will
probably think that anyway when they take over…Pete
On 21 Mar 2008, at 18:48, Walter Lee Davis wrote:
This looks like a Unix date, which is measured as the number of
seconds since January 1, 1970. PHP has the helpful tool strtotime(),
which will make a Unix date out of nearly any string. Yours becomes
1234674000.Walter
On Mar 21, 2008, at 2:27 PM, WebWorker wrote:
I’ve been asked to change some dates in a MYSQL database.
One date for example is 15 Feb 2009, and this appears in the
database as 1234677600.How does 15 Feb 2009 = 1234677600
Anyone been here before? :-/
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
At 14:44 -0400 21/3/08, WebWorker wrote:
Lets hope we all get there
Thanks for that…
But does MYSQL automatically convert dates into this date stamp? Or
do you have to have a PHP program converting a date into a date
stamp before uploading to the database?
In common with most, if not all, software that handles dates, MySql
stores a date as a single number not as a string. It not only makes
date manipulation easier, it makes a lot of things you expect and
need possible. It also takes less space. When you access the date
data from outside, even with say myphpadmin (phpmyadmin?), it shows
it as a string for you, but it’s still saved as a number.
David
–
David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
In this case, myphpadmin is showing the date as a number.
Are you saying myphpadmin should display the date as a text string? Or I can enter a text string in myphpadmin and it converts to a number? Or do I convert the date to a number first, then enter it in myphpadmin?
On 22 Mar. 2008, 8:28 am, David Ledger wrote:
When you access the date
data from outside, even with say myphpadmin (phpmyadmin?), it shows
it as a string for you, but it’s still saved as a number.
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
If you go to www.4webhelp.net/us/timestamp.php and enter
22nd March 1900 12:30:36 the number = -1
the date equals (Wednesday, December 31st 1969, 23:59:59 (GMT)
On 21 Mar. 2008, 7:38 pm, petemac wrote:
That is really fascinating. So that means that in addition to AD and
BC we could now think in terms of BU (before Unix) and say, AU (in
the year of Unix). So we are in the year 38AU. The computers will
probably think that anyway when they take over…
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
Presumably that would apply to any date before Jan 1 1970.
Pete
On 22 Mar 2008, at 12:41, WebWorker wrote:
If you go to www.4webhelp.net/us/timestamp.php and enter
22nd March 1900 12:30:36 the number = -1
the date equals (Wednesday, December 31st 1969, 23:59:59 (GMT)
On 21 Mar. 2008, 7:38 pm, petemac wrote:
That is really fascinating. So that means that in addition to AD and
BC we could now think in terms of BU (before Unix) and say, AU (in
the year of Unix). So we are in the year 38AU. The computers will
probably think that anyway when they take over…
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
Has no meaning? Try telling that to the robots…
On 21 Mar 2008, at 19:56, Walter Lee Davis wrote:
That moment is known as the Unix Epoch, and has no meaning other than
a simple point in the time-map
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
Actually, that’s not true. MySQL has its own Date format, and uses it
internally for any field that is specified as a Date, Time, Year,
DateTime or Timestamp. These dates usually take the form of ISO date-
time strings:
YYYY-MM-DD HH:MM:SS
There are obviously variations on that theme, but that’s the usual
format.
Many PHP/MySQL applications use the Unix timestamp format, simply
because it makes the math of dates that much easier to manage, and
also because there are lots of goodies baked into PHP itself for
handling these timestamps. When setting up a new PHP/MySQL
application to do this, you would specify the date field in your
database not as a MySQL Date type, but rather as a signed integer of
sufficient length to carry the particular date range you need to manage.
This is also a good practice if you are not sure that you are going
to stay in MySQL, since a signed integer is a very portable column
type across different database engines.
But if you want to be able to take advantage of MySQL’s date handling
magick in your queries, say to be able to say WHERE start_date BETWEEN '2001-12-1' AND '2002-12-1', then you need to use one of
MySQL’s Date column types in your database, and then format the
output in your own fashion (unless you like ISO dates for some
reason) for display.
Walter
On Mar 22, 2008, at 4:28 AM, David Ledger wrote:
At 14:44 -0400 21/3/08, WebWorker wrote:
Lets hope we all get there
Thanks for that…
But does MYSQL automatically convert dates into this date stamp? Or
do you have to have a PHP program converting a date into a date
stamp before uploading to the database?In common with most, if not all, software that handles dates, MySql
stores a date as a single number not as a string. It not only makes
date manipulation easier, it makes a lot of things you expect and
need possible. It also takes less space. When you access the date
data from outside, even with say myphpadmin (phpmyadmin?), it shows
it as a string for you, but it’s still saved as a number.David
–
David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options
At 11:48 -0400 22/3/08, Walter Lee Davis wrote:
Actually, that’s not true. MySQL has its own Date format, and uses it
internally for any field that is specified as a Date, Time, Year,
DateTime or Timestamp. These dates usually take the form of ISO date-
time strings:
On Mar 22, 2008, at 4:28 AM, David Ledger wrote:In common with most, if not all, software that handles dates, MySql
stores a date as a single number not as a string. It not only makes
date manipulation easier, it makes a lot of things you expect and
need possible. It also takes less space. When you access the date
data from outside, even with say myphpadmin (phpmyadmin?), it shows
it as a string for you, but it’s still saved as a number.
Oh well … another piece of ‘knowledge’ acquired from dba colleagues
over the years bites the dust …
David
–
David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options