Thread: 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 mySQL 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 applicablelaw. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution,or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you receivedthis transmission in error, please immediately contact the sender and destroy the material in its entirety, whetherin electronic or hard copy format. Thank you. A2
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-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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
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
-----------+------------------+-----------------+------------------
+------------------
| Joe | Blo | 1073244631.3063 |
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 = "SELECT login, firstname, lastname, date_part('epoch',
lastlogin), date_part('epoch', regdate) FROM admin ORDER BY login";
$result = pg_query($connection, $query) or die("Error in query:
$query. " . pg_last_error($connection));
$rows = pg_num_rows($result);
echo $rows;
//for($i=0; $i<$rows; $i++) {
//}
for ($i=0; $i<$rows; $i++)
{
$row = pg_fetch_array($result, $i, PGSQL_ASSOC);
?>
<tr>
<td width="24%"><? echo $row['login']; ?></td>
<td width="27%"><? echo $row['firstname']; ?></td>
<td width="23%"><? echo $row['lastname']; ?></td>
<?
//echo $row['lastlogin'];
$lastlogintime = $row['lastlogin'];
echo $lastlogintime."<BR><BR>";
$lastlogintime = strtotime($lastlogintime);
echo $lastlogintime."<BR><BR>";
?>
<td width="22%"> <!-- want to insert the lastlogin here --></td>
<td width="4%"><!-- want to insert the regdate here --> </td>
</tr>
<?
}
You need to switch your fetch mode to use column numbers, NOT column names. Chris
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.
--
__________
| |
| | Enver ALTIN (a.k.a. skyblue)
| | Software developer, IT consultant
| FRONT |
|==========| FrontSITE Bilgi Teknolojisi A.Ş.
|_____SITE_| http://www.frontsite.com.tr/
Attachment
>
>
>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.
>
>