SQL convert UTC to MST

I cannot figure this out, and im no SQL guru and would like to do this with=
an SQL statement if i can. so far here is my SQL statement.

SELECT * FROM t444d500009b5_4_6 ORDER BY utctime DESC

What can i add here to have the column in this table converted from UTC to =
MST.
any input would be helpful
Thanks,

Cameron Seader
Operations Center Technician II
CSeader@Idahopower.com
1.208.388.2582 Office



[INFO] -- Access Manager:
This transmission may contain information that is privileged, confidential =
and/or exempt from disclosure under applicable law. If you are not the int=
ended recipient, you are hereby notified that any disclosure, copying, dist=
ribution, or use of the information contained herein (including any relianc=
e thereon) is STRICTLY PROHIBITED. If you received this transmission in err=
or, please immediately contact the sender and destroy the material in its e=
ntirety, whether in electronic or hard copy format. Thank you. A2



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: SQL convert UTC to MST

On Sat, Dec 27, 2003 at 04:37:32PM -0700, Seader, Cameron wrote:
> I cannot figure this out, and im no SQL guru and would like to do this
> with an SQL statement if i can. so far here is my SQL statement.
>
> SELECT * FROM t444d500009b5_4_6 ORDER BY utctime DESC
>
> What can i add here to have the column in this table converted from
> UTC to MST.

What data type is the utctime field? AT TIME ZONE converts between time
zones, but its use varies depending on the data type you're working with.

http://www.postgresql.org/docs/current/static/functions-date time.html#FUNCTIONS-DATETIME-ZONECONVERT

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: SQL convert UTC to MST

On Saturday 27 December 2003 3:37 pm, Seader, Cameron wrote:
> I cannot figure this out, and im no SQL guru and would like to do
> this with an SQL statement if i can. so far here is my SQL
> statement.
>
> SELECT * FROM t444d500009b5_4_6 ORDER BY utctime DESC
>
> What can i add here to have the column in this table converted from
> UTC to MST. any input would be helpful
> Thanks,

Depends. If the data is an INT (seconds from the epoch) and your
timezone is set correctly then abstime(utctime) should give you local
time (adjusted appropriately for Daylight Saving).

What is the data type of utctime?

Cheers,
Steve


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

displaying a rowset in php...same column names

This query: SELECT login, firstname, lastname, date_part('epoch',
lastlogin), date_part('epoch', regdate) FROM admin ORDER BY login

results in:

login | firstname | lastname | date_part | =
=20
date_part
-----------+------------------+-----------------+----------- -------=20
+------------------
| Joe | Blo | 1073244631.3063 | =
=20
1073244631.3063
M.too | Me | too | 1073245739.87669 |
1073245739.87669
admin | admin first name | admin last name | 1073166434.11792 |
1073166434.11792
m.four | Me | four | 1073246991.60247 |
1073246991.60247
m.three | Me | three | 1073246781.73784 |
1073246781.73784
superuser | admin first name | admin last name | 1073166417.11391 |
1073166417.11391
(6 rows)


with two columns of the same name. how do I reference those columns
individually in PHP 4.32?

One try:


$query =3D "SELECT login, firstname, lastname, date_part('epoch',
lastlogin), date_part('epoch', regdate) FROM admin ORDER BY login";
$result =3D pg_query($connection, $query) or die("Error in query:
$query. " . pg_last_error($connection));
=09
$rows =3D pg_num_rows($result);
=09
echo $rows;

//for($i=3D0; $i<$rows; $i++) {

//}
=09

for ($i=3D0; $i<$rows; $i++)
{
$row =3D pg_fetch_array($result, $i, PGSQL_ASSOC);
?>





//echo $row['lastlogin'];
$lastlogintime =3D $row['lastlogin'];
echo $lastlogintime."

";
$lastlogintime =3D strtotime($lastlogintime);
echo $lastlogintime."

";

?>



}


how do I get this to work? do I need to convert the $row to a 2d php
array? How do I do that?

Michael


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: displaying a rowset in php...same column names

You need to switch your fetch mode to use column numbers, NOT column names.

Chris


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: displaying a rowset in php...same column names

--=-m9xbbsBhuKPbDv0RqSZJ
Content-Type: text/plain; charset=iso-8859-9
Content-Transfer-Encoding: quoted-printable

On Wed, 2004-01-07 at 05:09, Michael Hanna wrote:
> This query: SELECT login, firstname, lastname, date_part('epoch',
> lastlogin), date_part('epoch', regdate) FROM admin ORDER BY login
> results in:
>=20
> login | firstname | lastname | date_part |=20=

> date_part

SELECT foo as bar,bar as foo from footable

notice the "as" thingie. It would help.
--=20
__________
| |
| | Enver ALTIN (a.k.a. skyblue)
| | Software developer, IT consultant
| FRONT |
|==========| FrontSITE Bilgi Teknolojisi A.=DE.
|_____SITE_| http://www.frontsite.com.tr/

--=-m9xbbsBhuKPbDv0RqSZJ
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQA/++oaZCB2FZvqK0sRAu2BAJ49w67SpDyd71AydY93cMTaD/NRFQCf aSFY
e5UvLvwS52P0tsKB6fHYPng=
=ScyY
-----END PGP SIGNATURE-----

--=-m9xbbsBhuKPbDv0RqSZJ--

Re: displaying a rowset in php...same column names

>
>
>This query: SELECT login, firstname, lastname, date_part('epoch',
>lastlogin), date_part('epoch', regdate) FROM admin ORDER BY login
>results in:
>
> login | firstname | lastname | date_part |
>date_part
>
>

Another way from assigning column names, if you dont want to do that...

instead of pg_Fetch_Object

$data = pg_Fetch_Array($result, $row);

/*
$data[0]: login
$data[1]: firstname
$data[2]: lastname
$data[3]: date_part
$data[4]: date_part
*/

*or*

$login = pg_Result($result, 0, 0);
$firstname = pg_Result($result, 1, 0);
$lastname = pg_Result($result, 2, 0);
$date1 = pg_Result($result, 3, 0);
$date2 = pg_Result($result, 4, 0);

Hope this helps

Gavin

Enver ALTIN wrote:

>On Wed, 2004-01-07 at 05:09, Michael Hanna wrote:
>
>
>>This query: SELECT login, firstname, lastname, date_part('epoch',
>>lastlogin), date_part('epoch', regdate) FROM admin ORDER BY login
>>results in:
>>
>> login | firstname | lastname | date_part |
>>date_part
>>
>>
>
>SELECT foo as bar,bar as foo from footable
>
>notice the "as" thingie. It would help.
>
>



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings