#1
  1. No Profile Picture
    Robert_J_Sherman
    Guest
    Devshed Newbie (0 - 499 posts)
    Okay, as I look over the docs on MySQL, and
    I'm assuming MS SQL will be quite similar.

    I am trying to decide how best to pull this off.

    I am anticipating a need to take the typical SQL TIMESTAMP and convert it to an Unix Timestamp...

    Now, as I look at this a TIMESTAMP stores namely a "numerical" format in a 14 Digit String.

    I'm just wondering if anyone has a suggestion as to how I might split this into the componenets (4d Year/3d Month/2d Day/HH MM SS) so that it could be passed through mktime() (which will give me the unix version).

    Actually, I think I may have an approach..

    perhaps using the php substr() function..

    since it returns a "defined" portion of a string..

    if $x was our timestamp, we'd have to pass it through the substr() function a few times, to pull out the pieces we wanted..

    dunno..

    ------------------
    SnR Graphics,
    Low Cost Hosting and Web Development.
  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,642
    Rep Power
    4492
    Does MSSQL have a unix_timestamp() function like MySQL??

    mysql> select UNIX_TIMESTAMP();
    -> 882226357
    mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
    -> 875996580

    ---John Holmes...

    ------------------
    *************************************************************
    * The manual can probably answer 90% of your questions...
    *
    * PHP Manual. www.php.net/manual
    * MySQL Manual: www.mysql.com/documentation/mysql/bychapter
    *************************************************************
  4. #3
  5. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Location
    Sydney, Australia
    Posts
    5
    Rep Power
    0
  6. #4
  7. No Profile Picture
    Robert_J_Sherman
    Guest
    Devshed Newbie (0 - 499 posts)
    I really haven't a clue what access has, chances are 'no'... it's an MS product, and like all of their products, doesn't play well with others.

    My thing is, that I would be converting this date, from an incoming file, and not actually comming out of the database.

    I tend to store unix timestamps in my db tables.. So.. I will need to convert any date being uploaded...

    Not sure how I'm going to pull it all off.. but....

    ------------------
    SnR Graphics,
    Low Cost Hosting and Web Development.
  8. #5
  9. No Profile Picture
    Robert_J_Sherman
    Guest
    Devshed Newbie (0 - 499 posts)
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    Does MSSQL have a unix_timestamp() function like MySQL??

    mysql> select UNIX_TIMESTAMP();
    -> 882226357
    mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
    -> 875996580

    ---John Holmes...

    [/quote]


    Learn something new every day

    Didn't see that function in the stuff on MySQL.. then again, guess I didn't look for it.

    Frankly, I tried using the "TIMESTAMP" once before in a db table, and didn't like the results.. so I started simply inputing
    the unix timestamp in a varchar field.

    This hasn't been a problem, as I'm always reading the database via PHP, so translating that to human form is no problem.

    Course, the cause for my question, is because I'm working on a project where the site owners want an "upload" "import" feature, so users can import data into the
    online database.

    I naturally assume that someone uploading data will be importing via a flat file, and their date(s) will be in the "software" format.

    Most likely coming from an excel or access flat file.

    So, I would have to manipulate their date, into my format, prior to inserting it into my database.

    Hence the question...


    ------------------
    SnR Graphics,
    Low Cost Hosting and Web Development.
  10. #6
  11. No Profile Picture
    Robert_J_Sherman
    Guest
    Devshed Newbie (0 - 499 posts)
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by grahamg:
    Have a look here http://www.php.net/manual/function.date.php

    or here
    http://www.phpbuilder.com/columns/akent20000610.php3

    HTH
    Graham
    [/quote]


    I appreciate the reply, but I must say that I'm quite adept at manipulating the date strings. Quite familiar with mktime() and date().. along with a few of the others.

    In this particular case, those don't answer the question, since dates stored in the sql database are stored in a sequence of numbers
    formatted like: YYYYMMDDHHMMSS, and from what I've read they are rarely separated by any delimeter like a "-".

    This means that in order to translate it, into any format, it would have to be done either by SQL or some other interpretor.

    Problem, in my case, is that I need to translate it "before" inserting it into the database.

    which is where something like substr() would come in handy..

    substr() can be told how much of the $string to read, and.. making it possible to read say just the first 4 digits, ie (YYYY)..
    so, to read the year a substr() call would be something like:

    $year = substr($date,0,4);

    There was an example of this under the "date()" function, on PHP.net's site..

    along with other ideas..



    ------------------
    SnR Graphics,
    Low Cost Hosting and Web Development.
  12. #7
  13. No Profile Picture
    Robert_J_Sherman
    Guest
    Devshed Newbie (0 - 499 posts)
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    Does MSSQL have a unix_timestamp() function like MySQL??

    mysql> select UNIX_TIMESTAMP();
    -> 882226357
    mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
    -> 875996580

    ---John Holmes...

    [/quote]

    Actually, this function makes me ask the question, do SQL have an "oposite".. reading a unix string, into it's format



    ------------------
    SnR Graphics,
    Low Cost Hosting and Web Development.
  14. #8
  15. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Yes. from_unixtime().

    As far as converting the mysql timestamp in integer form you can use substr() to parse it out into it's elements and use those.

    $year=substr($mysqlts,0,4);
    $month=substr($mysqlts,3,2);
    $day=substr($mysqlts,5,2);
    $hour=substr($mysqlts,7,2);
    $mins=substr($mysqlts,9,2);
    $secs=substr($mysqlts,11,2);

    HTH

    Rod
  16. #9
  17. No Profile Picture
    Robert_J_Sherman
    Guest
    Devshed Newbie (0 - 499 posts)
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    Yes. from_unixtime().

    As far as converting the mysql timestamp in integer form you can use substr() to parse it out into it's elements and use those.

    $year=substr($mysqlts,0,4);
    $month=substr($mysqlts,3,2);
    $day=substr($mysqlts,5,2);
    $hour=substr($mysqlts,7,2);
    $mins=substr($mysqlts,9,2);
    $secs=substr($mysqlts,11,2);

    HTH

    Rod
    [/quote]

    Kinda what I thought.

    Naturally the tricky part here, is going to be parsing the lines of the incoming file, and performing this conversion

    Thanks for the input Rod ...

    ------------------
    SnR Graphics,
    Low Cost Hosting and Web Development.
  18. #10
  19. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,642
    Rep Power
    4492
    Do you know what the format of this date will be in the flat text files the client wants to upload? Mysql will sometimes "understand" what you mean, even with the date formated in different ways. Can you post an example?

    You know that MySQL can upload a comma or tab delimited file using LOAD DATA INFILE 'data.txt' INTO your_table command, right?? (Check that syntax, though, that's off the brain...)

    In my opinion, I think it's always better to use a timestamp column in mysql. Why try to get around the code of the database with another programming language?? If you're trying to store the current date, it's updated automatically. You can use the functions TO_UNIXTIME() and FROM_UNIXTIME() if that's all your given is a unix timestamp. Plus....the one everybody always overlooks, you can use the DATE_FORMAT() function in MySQL to format the timestamp however you want it. Even though the format in the table is YYYYMMDDhhmmss, you can pull out full month names, 2 or 4 digit years, format it however you want. It's similar to PHPs date() function.

    The reason I point all this out is why write all of this extra PHP code to format dates, when you can just pull it out of the database already formated. You just print it out.

    MySQL has A LOT of functions that you can use to format almost any kind of data...read up on them if you get a chance...

    ---John Holmes...

    ------------------
    *************************************************************
    * The manual can probably answer 90% of your questions...
    *
    * PHP Manual. www.php.net/manual
    * MySQL Manual: www.mysql.com/documentation/mysql/bychapter
    *************************************************************
  20. #11
  21. No Profile Picture
    Robert_J_Sherman
    Guest
    Devshed Newbie (0 - 499 posts)
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    Do you know what the format of this date will be in the flat text files the client wants to upload?


    Nope, no idea.. I'm just speculating that there will be a date.. I have asked for a copy of their file(s).. so I can script around their file format.


    Mysql will sometimes "understand" what you mean, even with the date formated in different ways. Can you post an example?


    Presently I can't, waiting to receive a copy.


    You know that MySQL can upload a comma or tab delimited file using LOAD DATA INFILE 'data.txt' INTO your_table command, right?? (Check that syntax, though, that's off the brain...)


    Yes you are correct about that.. but I first
    have to get the file up there.. In conjunction with those commands are the "OPTIONALLY ..." elements, which allow us
    to define the delimters and "encapsulation".


    In my opinion, I think it's always better to use a timestamp column in mysql. Why try to get around the code of the database with another programming language?? If you're trying to store the current date, it's updated automatically. You can use the functions TO_UNIXTIME() and FROM_UNIXTIME() if that's all your given is a unix timestamp. Plus....the one everybody always overlooks, you can use the DATE_FORMAT() function in MySQL to format the timestamp however you want it. Even though the format in the table is YYYYMMDDhhmmss, you can pull out full month names, 2 or 4 digit years, format it however you want. It's similar to PHPs date() function.


    I can see how those "TO_" and "FROM_" functions will come in handy. Honestly, to
    date the bulk of my database interfacing has
    been done via PHP and the browser.. no real
    direct interaction.

    I still have a lot to learn about MySQL.. and that's no lie


    The reason I point all this out is why write all of this extra PHP code to format dates, when you can just pull it out of the database already formated. You just print it out.


    IMHO the coding is about the same, either way. At least for what I've done so far.


    MySQL has A LOT of functions that you can use to format almost any kind of data...read up on them if you get a chance...


    It looks like I will.. but I'm sure you're a lot like me, and time is something we never have enough of.

    I've only been working with PHP and MySQL about 6 months.. well, closer to 8 or 9 now.

    I know I have a lot to learn.. But there again, learning is an "everyday" thing.


    ---John Holmes...

    [/quote]



    ------------------
    SnR Graphics,
    Low Cost Hosting and Web Development.
  22. #12
  23. No Profile Picture
    Robert_J_Sherman
    Guest
    Devshed Newbie (0 - 499 posts)
    Turns out I don't have to worry about it all.

    The file they are using is the ole standard pipe delimited flat file, with their date formatted as DD-MM-YY.

    We've decided to modify the table that the data will be recorded in, one that better suits the data coming in.. seems our initial information wasn't entirely accurate.

    ------------------
    SnR Graphics,
    Low Cost Hosting and Web Development.

Similar Threads

  1. Converting unix timestamp to date
    By hellbrand in forum XML Programming
    Replies: 1
    Last Post: February 10th, 2004, 01:00 PM
  2. unix apache ODBC to MS SQL
    By Andy_Teather in forum MS SQL Development
    Replies: 0
    Last Post: February 9th, 2004, 02:52 AM
  3. unix apache ODBC to MS SQL
    By Andy_Teather in forum ASP Programming
    Replies: 0
    Last Post: February 9th, 2004, 02:48 AM
  4. COnverting from Access to SQL
    By m3ckon in forum ASP Programming
    Replies: 2
    Last Post: November 6th, 2003, 08:01 AM
  5. Poor SQL Server Programming, Part 4
    By DavidM in forum MS SQL Development
    Replies: 0
    Last Post: October 21st, 2003, 10:28 PM

IMN logo majestic logo threadwatch logo seochat tools logo