sql - oracle convert timestamps to other time masks -


how convert these number columns (timestamp, event_dt) date or time mask? trying this:

    select to_char(timestamp,'yyyy-mon-dd hh24:mi:ss'), domain_c, to_char(event_date,'yyyy-mon-dd hh24:mi:ss'), total_reads top_read_events timestamp= to_char(sysdate-2,'yyyymmdd') || '0000'                          * error @ line 1: ora-01481: invalid number format model   sql> desc top_read_events;  name                      null?    type  ----------------------------------------- -------- ----------------------------  year                           number  quarter                        number  month                          number  day                            number  hour                           number  timestamp                      number  domain_c                       varchar2(255)  event_dt                       number  total_reads                        number     select timestamp, domain_c, event_dt, total_reads top_read_events timestamp= to_char(sysdate-2,'yyyymmdd') || '0000'       timestamp domain_c              event_dt  total_reads ---------- ------------------------------  ------------ ------------- 2.0111e+11 b.e.att-mail.com         2.0111e+11         14406 2.0111e+11 bounce.emailinfo2.bestbuy.com    2.0111e+11         14156 2.0111e+11 bounce.bluestatedigital.com      2.0111e+11         13701 2.0111e+11 plentyoffish.com         2.0111e+11         13384 2.0111e+11 mail.classmates.com          2.0111e+11         13281 2.0111e+11 comcast.net              2.0111e+11         13241 2.0111e+11 uniquelistsmail.com          2.0111e+11         13135 2.0111e+11 tankgorilla.com          2.0111e+11         12835 2.0111e+11 frigidphoenix.com            2.0111e+11         12657

firstly, never store date or timestamp data-types in date or timestamp column. causes no end of pain; you're gathering.

as "timestamps" have 11 orders of magnitude i'm going guess inserted them in form yyyymmddhh24mi, , they're not seconds since unspecified epoch or that.

if want convert have firstly convert them character, date. don't need timestamp these differ dates in fractional seconds.

it'd following:

select to_date(to_char(timestamp),'yyyymmddhh24mi') my_timestamp   top_read_events

on side note never call column timestamp, date, group or reserved word. causes many problems. go "tstamp" that's personal preference.

if want convert "timestamp" character you'd have convert character again.

select to_char(to_date(to_char(timestamp)                        ,'yyyymmddhh24mi')               ,'yyyy-mon-dd hh24:mi:ss') my_timestamp   top_read_events

the seconds 00 don't have these. note use explicit number character date character conversion. makes more obvious coders come after you're doing, including 2 years down line, , there's no chance of interpreter making mistake on intention , comparing number character etc.

for reason i'd change where timestamp= to_char(sysdate-2,'yyyymmdd') || '0000'. want date comparison change date , way, i.e.

where trunc(to_date(to_char(timestamp),'yyyymmddhh24mi'),'dd') = trunc(sysdate -2)

trunc() truncates @ day level in instance.


Comments

Post a Comment

Popular posts from this blog

jquery - Invalid Assignment Left-Hand Side -

i trying assign class tag via jquery, resulting in error through firebug says "invalid assignment left-hand side" on fourth line below. <cfoutput> $('.pngfix a').each(function(index) { var hreflink = $(this).attr("href"); if (hreflink.tolowercase() = '../audio.cfm') { $(this).addclass('audioimg'); } }); </cfoutput> looking around online, looks syntax problem, don't seem see it... any tips? thanks if (hreflink.tolowercase() = '../audio.cfm') that attempts assign value, rather check equality. change = operator == .
Read more

java - Play! framework 2.0: How to display multiple image? -

i need display gallery of photos. here template: @(photos: list[photo]) @title = { <bold>gallery</bold> } @main(title,"photo"){ <ul class="thumbnails"> @for(photo <- photos) { <li class="span3"> <a href="#" class="thumbnail"> <img src="@photo.path" alt=""> </a> </li> } </ul> } and here controller method: public static result getphotos() { return ok(views.html.photo.gallery.render(photo.get())); } and here photo bean : @entity public class photo extends model { @id public long id; @required public string label; public string path; public photo(string path, string label) { this.path = path; this.label = label; } private static finder<long, photo> find = new finder<long, photo>( long.class, photo.class); public static list<phot...
Read more

gmail - Is there any documentation for read-only access to the Google Contacts API? -

i want read-only access user's google contacts. there stackoverflow member wanted this, , received following answer : yes, there is, use https://www.googleapis.com/auth/contacts.readonly scope , "view contacts". this exchange reference read-only access google contacts can find using google. simply using access token obtained attempt read usual google contacts api (i.e. https://www.google.com/m8/feeds ) fails insufficient permissions. similarly, using google contacts api on new target fails 404 errors (for example https://www.googleapis.com/auth/contacts.readonly/default/full ). is there documentation anywhere on how use api, or permissible way access google contacts api full read-write access?
Read more